Why PostgreSQL volatile function is faster than inline subquery?

The following examples have been tested in PostgreSQL versions 11 and 12.

A few of the tables involved have around 2m rows, others not more than 250k.

I have very big and nested queries like this (not the actual query, just an example):

SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM
  (SELECT table2.some_id AS label4,
                       table2.id AS label5,
                       table3.some_date AS label6,
                       CASE
                           WHEN (table2.some_value IS NOT NULL
                                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
                           ELSE table4.table4
                       END AS label6
   FROM table3
   JOIN
     (SELECT coalesce(table5.table5) AS table4,
             coalesce(table5.table5_1) AS table5_1,
             coalesce(table5.table5_2) AS table5_2,
             coalesce(table5.table5_3) AS table54_3
      FROM
        (SELECT table2.some_value * 1 AS table5,
                table2.some_id AS table5_some_id,
                table2.id AS table5_id,
                table3.some_date AS table5_some_date
         FROM table3
         JOIN table2 ON table2.some_other_id IN
           (SELECT unnest(something_nested.ids) AS unnest_1
            FROM
              (SELECT ...

The query above takes around 49s to execute.

Then I decided to convert some of these subqueries into functions. It was literally just a cut/paste of some pieces of the big query, resulting in a final query that reads like this:

SELECT coalesce(column1, 1) AS label1,
       coalesce(column2, 2) AS label2,
       coalesce(column3, 3) AS label3
FROM some_function()

-- some_function created as "STABLE":
SELECT table2.some_id AS label4,
       table2.id AS label5,
       table3.some_date AS label6,
       CASE
           WHEN (table2.some_value IS NOT NULL
                 OR table2.some_other_value IS NOT NULL) THEN least(table2.some_value * 1, greatest(table4.table4, table2.some_other_value * 1))
           ELSE table4.table4
       END AS label6
FROM some_other_function()

The query above takes the same 49s to execute.

However, as soon as I changed one function (any of them) to VOLATILE, the query magically executes in less than 250ms.

My questions are:

  • Why VOLATILE is WAY faster than STABLE in this case? Is it safe to use VOLATILE?
  • Why declaring subqueries as function executes faster than just inlining them? Is there a flag or some way to optimise this kind of scenario?

Thanks

css – Gmail removing certain inline styles

I’m making a signature template for my company, and I want to remove the underline from the links. When I first paste it into the signature creator in gmail, there is no underline. When I use dev tools to inspect the signature inside the signature creator, my text-decoration: none !important is there. BUT, when I go to use the signature, in a new email or whatever, the links are underlined, and the text-decoration style is gone.

I also tried declaring text-decoration: none on a span tag inside the a tag and adding text-decoration-color: white to the a and the span. All of these style tags are removed by gmail.

I tried adding spaces inside of the link, because I saw that somewhere, it didn’t work.

I tried using dev tools to remove the underline when the signature is in the email, copying and pasting it back into the signature creator, but the same thing happened.

I tried adding a the style rule to the tag, didn’t make a difference.

Does anyone have any insights for me?

xss – Is nonce useless when user input is reflected within an inline script?

I stumbled upon a web app which is accepting user input and putting it into a variable within script tag.

The script tag does have a nonce attribute.

enter image description here

As am working on bypassing the XSS filter, I had this thought that this practice of reflecting user input within an inline script with nonce attribute renders nonce useless.

Is my understand correct or am I missing something here ?

bash – Inserting HTML (with inline styling) to MySQL via command line

I’m trying to automate a Magento site build and one of the challenges I’m having is updating the content of a page in the database through a command. When trying to do:

mysql -D magento -e "INSERT INTO cms_page ('content') VALUES ("<p style="text-align: center;"><a href="http://www.magentocommerce.com/knowledge-base"><img src="{{media url=&quot;36d3c9416834b86ba9a78b92d97325f556a2f32f.png&quot;}}" alt="" width="1200" height="630"></a></p>");"

I’m receiving this error:

-bash: syntax error near unexpected token `<'

I’m by no means a DBA, but I have a feeling the issue is that I’m not properly escaping the double quotes, or that it’s running into the terminating ‘;’ in the middle of the HTML and failing.

Any help would be appreciated.

How can I refactor a function to be inline?

I’m refactoring functions trying to do more of a in-line aproach.

        private int GetRandomNumberHigherThanHalf()
        {
            double randomNumber= Random.NextDouble();

            if (randomNumber < 0.5)
                throw new Exception("randomNumber is lower than 0.5");

            return randomNumber;
        }

Any ideas about how can I refactor the function above to one line? It has to trhow an Exception and return the number generated.

html – Como aplicar un subrayado en un bloque inline

Muy buenas, tengo un problema tratando de imitar el diseño de este elemento.

introducir la descripción de la imagen aquí

Quiero colocar ese “subrayado” azul con el espacio en el top, sin embargo, no encuentro la solución. Creo que tengo
que aplicar el pseudoelemento::after pero me he complicado el doble.

c# – Translating emails inline using HTML Agility

I am using Google Cloud API and Mailkit to translate emails. I’m using HTML Agility to parse the HTML from the emails and translate them, but what I want to do is replace the Inner HTML text with the translated text if possible. This is what I have currently –

public class TranslationService : ITranslationService
{
    private readonly string targetLang = "en";
    private readonly string projectId = "bla";

    public string Translate(string text)
    {
        HtmlDocument mainDoc = new HtmlDocument();
        mainDoc.LoadHtml(text);
        string cleanText = mainDoc.DocumentNode.InnerText;

       (translates and returns the clean text)

       string returnText = mainDoc.DocumentNode.OuterHtml.Replace(cleanText, translatedText);

        return returnText;
    }
}

Can anyone help with this problem?
Thanks!

filters – Inline Styles on all native blocks

Is it possible to apply inline styles to all blocks via filters? Or do only some blocks support it? basically I want to add the ability of adding a background image to a single column.
So I added an extra attribute to store the background img url (named backgroundImg) and tested that its saved.

I am trying to use blocks.getSaveContent.extraProps to apply the background image to the inline style like so..

if ("wp-block-column" === props.className) {
    return lodash.merge(props, {
        style: {
            backgroundImage: "url(" + props.backgroundImg + ")",
        },
    });
}

When I run this however the background IMG url is ‘undefined’. However I have tested and know that props.backgroundImg is being saved. I can add more of my code as needed, but any ideas on why it isn’t applying the saved url?

8 – Inline entity form module fails when creating a new node and the reference method is Views

I have a Drupal 8, core version:8.8.x project, i’m using inline entity form module(latest version) for entity reference fields. Also i have tried using entity connect module but it caused various issues. The problem is when creating a new node, using the inline entity form and the reference method of field is Views: Filter by an entity reference view this error is coming up:

This entity (node: newNode) cannot be referenced.

How to reproduce:

  1. Install inline entity form module
  2. Create an entity reference field in any content type with Views: Filter by an entity reference view as reference method.
  3. To your content type manage form display choose inline entity form – complex for widget.
  4. Add new node for your content type, press add new node to your reference field, fill the modal form, publish it and press create new node(referenced node).
  5. Lastly publish and save your node.

I tried some patches published to project issues but most of them are not applying at all. I would appreciate any ideas for solving it or suggestions for using a similar module.

How in-line link URL with ) and # in Reddit?

The # after a path refers to the DOM Element with the id following the #.

The only reason for this not to work on reddit is, if they somehow dont allow links containing the # character. In this case, there is no way for you to specifically link to the id.