Re: Query much slower from php than psql or dbeaver

[Ekaterina Amez]

El vie, 21 ene 2022 a las 5:04, Michael Lewis ()
escribió:

> When dealing with foreign tables, I believe planning is not the same
> because of access to statistics (maybe has improved since 9.6 though). I
> just wonder... Would it be a viable option to create a materialized view
> using the FDW but then use the PHP script against the local tables only?
> Materialized views are not maintained automatically, but you have local
> statistics and can create indexes. Just a thought in case the data is not
> changing constantly and this might fit the need.
>
> Also, it seems like perhaps the foreign queries might be more targeted if
> some data was encouraged to be pre-computed. What would be the expected row
> count from just table1?
>
> Note- your explain plan doesn't mention "fichero_origen" field name. Is
> that just a copy/paste error?
>

It's a column renaming mistake.
t1.fichero_origen = t1.file (it's the column where incoming file name is
stored).



>
> with cte_interesting_t1_rows_precomputed AS materialized(
> select
> t1.*,
> substring(t1.bbb from 1 for 3) in () AND t1.file =
> 'file_name.csv' AS needs_t2,
> substring(t1.bbb from 1 for 3) in () AS needs_t3
> FROM
> table1 t1
> where t1.c = 'ACTIVE'
> and t1.fichero_origen = 'file_name.csv'
> )
>
>  select t1. as maindb_, t1.bbb as maindb_bbb, t1.c as
> maindb_c, t1.timestamp_create as maindb_create,
> t1.timestamp_closed as maindb_close, t1.d as maindb_d,
> null::text as db1_sth,
> t2. as db1_, t2. as db1_, null::text
> as db2_sth,
> t3. as db2_, t3. as db2_
> from cte_interesting_t1_rows_precomputed AS t1
> left join database1_fdw.table2 AS t2 on t1. = t2.btatpd_ and
> t2.btatpd_fecha = '2022011912' AND needs_t2
> left join database2_fdw.table2 AS t3 on t1. = t3.btatpd_ and
> t3.btatpd_fecha = '2022011912' AND needs_t3
> where
> (t2. is null and t3. is null)
> or
> (t2. is not null and t1.d <> t2.)
> or
> (t3. is not null and t1.d <> t3.)
> order by t1.bbb nulls last;
>

I'm doing some tests after reading carefully your answer and Tom's, and
I'll post back.
Thank you both.

Re: Query much slower from php than psql or dbeaver

[Michael Lewis]

When dealing with foreign tables, I believe planning is not the same
because of access to statistics (maybe has improved since 9.6 though). I
just wonder... Would it be a viable option to create a materialized view
using the FDW but then use the PHP script against the local tables only?
Materialized views are not maintained automatically, but you have local
statistics and can create indexes. Just a thought in case the data is not
changing constantly and this might fit the need.

Also, it seems like perhaps the foreign queries might be more targeted if
some data was encouraged to be pre-computed. What would be the expected row
count from just table1?

Note- your explain plan doesn't mention "fichero_origen" field name. Is
that just a copy/paste error?


with cte_interesting_t1_rows_precomputed AS materialized(
select
t1.*,
substring(t1.bbb from 1 for 3) in () AND t1.file =
'file_name.csv' AS needs_t2,
substring(t1.bbb from 1 for 3) in () AS needs_t3
FROM
table1 t1
where t1.c = 'ACTIVE'
and t1.fichero_origen = 'file_name.csv'
)

 select t1. as maindb_, t1.bbb as maindb_bbb, t1.c as
maindb_c, t1.timestamp_create as maindb_create,
t1.timestamp_closed as maindb_close, t1.d as maindb_d,
null::text as db1_sth,
t2. as db1_, t2. as db1_, null::text as
db2_sth,
t3. as db2_, t3. as db2_
from cte_interesting_t1_rows_precomputed AS t1
left join database1_fdw.table2 AS t2 on t1. = t2.btatpd_ and
t2.btatpd_fecha = '2022011912' AND needs_t2
left join database2_fdw.table2 AS t3 on t1. = t3.btatpd_ and
t3.btatpd_fecha = '2022011912' AND needs_t3
where
(t2. is null and t3. is null)
or
(t2. is not null and t1.d <> t2.)
or
(t3. is not null and t1.d <> t3.)
order by t1.bbb nulls last;

Re: Multiple SELECT statements Using One WITH statement

[David G. Johnston]

On Thu, Jan 20, 2022 at 4:34 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Jan 20, 2022 at 4:32 PM Ken Tanzer  wrote:
>
>>
>>
>> On Thu, Jan 20, 2022 at 8:46 AM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> You can always write:
>>>
>>> CREATE VIEW cte_view AS
>>> WITH cte AS (...)
>>> SELECT * FROM cte;
>>>
>>> And then incorporate that into any queries that require the results of
>>> said CTE.
>>>
>>>
>> Is there any advantage to still using a CTE inside the view
>> definition, and then selecting it?  Instead of just
>>
>> CREATE VIEW cte_view AS
>> (...);
>>
>> Just curious, in case I'm missing something!
>>
>>
> No. That would be tunnel vision and early morning posting on my part.
>
>
Though I suspect if the CTE had a MATERIALIZED modifier there would be a
difference.  You can force a view to be materialized.

David J.

Re: Multiple SELECT statements Using One WITH statement

[David G. Johnston]

On Thu, Jan 20, 2022 at 4:32 PM Ken Tanzer  wrote:

>
>
> On Thu, Jan 20, 2022 at 8:46 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> You can always write:
>>
>> CREATE VIEW cte_view AS
>> WITH cte AS (...)
>> SELECT * FROM cte;
>>
>> And then incorporate that into any queries that require the results of
>> said CTE.
>>
>>
> Is there any advantage to still using a CTE inside the view
> definition, and then selecting it?  Instead of just
>
> CREATE VIEW cte_view AS
> (...);
>
> Just curious, in case I'm missing something!
>
>
No. That would be tunnel vision and early morning posting on my part.

David J.

Re: Multiple SELECT statements Using One WITH statement

[Ken Tanzer]

On Thu, Jan 20, 2022 at 8:46 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> You can always write:
>
> CREATE VIEW cte_view AS
> WITH cte AS (...)
> SELECT * FROM cte;
>
> And then incorporate that into any queries that require the results of
> said CTE.
>
>
Is there any advantage to still using a CTE inside the view definition, and
then selecting it?  Instead of just

CREATE VIEW cte_view AS
(...);

Just curious, in case I'm missing something!

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: psql does not provide proper response

[Adrian Klaver]

On 1/20/22 12:35, Shaozhong SHI wrote:

Added.  But only head of columns appeared.


Best guess is there is no data in table.

Do:

select count(*) from boundaryline.scotland_and_wales_const_region;


Any way to visualise?
Regards, David

On Thursday, 20 January 2022, Rob Sargent > wrote:


On 1/20/22 10:54, Shaozhong SHI wrote:

I do not know what happened.

psql does not provide proper response anymore.

I typed the following and see nothing.

user=# select * from boundaryline.scotland_and_wales_const_region
user-#

Can anyone enlighten me?

Regards,

David

Add semi-colon return?




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: psql does not provide proper response

[David G. Johnston]

On Thu, Jan 20, 2022 at 1:35 PM Shaozhong SHI 
wrote:

> Added.  But only head of columns appeared.


Then the table is probably empty...

Any way to visualise?
>

Visualize what?  You do realize that psql is a text-based application,
right?

David J.

Re: psql does not provide proper response

[Shaozhong SHI]

Added.  But only head of columns appeared.
Any way to visualise?
Regards, David

On Thursday, 20 January 2022, Rob Sargent  wrote:

> On 1/20/22 10:54, Shaozhong SHI wrote:
>
> I do not know what happened.
>
> psql does not provide proper response anymore.
>
> I typed the following and see nothing.
>
> user=# select * from boundaryline.scotland_and_wales_const_region
> user-#
>
> Can anyone enlighten me?
>
> Regards,
>
> David
>
> Add semi-colon return?
>

Re: psql does not provide proper response

[David G. Johnston]

On Thu, Jan 20, 2022 at 10:55 AM Shaozhong SHI 
wrote:

> I do not know what happened.
>
> psql does not provide proper response anymore.
>
> I typed the following and see nothing.
>
> user=# select * from boundaryline.scotland_and_wales_const_region
> user-#
>
> Can anyone enlighten me?
>
>
You got and are showing the proper response, the prompt changed from
"user=#" to "user-#".

The reason it did that response instead of showing a query result has
already been answered.

David J.

Re: psql does not provide proper response

[Pavel Stehule]

čt 20. 1. 2022 v 19:50 odesílatel Bryn Llewellyn  napsal:

> > shishaozh...@gmail.com wrote:
> >
> > I do not know what happened.
> >
> > psql does not provide proper response anymore.
> >
> > I typed the following and see nothing.
> >
> > user=# select * from boundaryline.scotland_and_wales_const_region
> > user-#
> >
> > Can anyone enlighten me?
>

you are missing to write semicolon - at end of command

you see it in prompt "-#"

Regards

Pavel


>
> This happens to me all too frequently. Then I kick myself and realize that
> I earlier said “\o spool.txt” (by hand) and forgot to turn it off with the
> bare “\o”. This is my typical use case:
>
> \o spool.txt
> \i script.sql
>
> The script causes an error. I read it, see the bad statement, and then
> type the correct statement by hand. Then I see what you did: a big fat
> nothing.
>
> Might this be happening to you?
>
>
>
>

Re: [EXT] Re: Can we get the CTID value

[David G. Johnston]

On Thu, Jan 20, 2022 at 11:36 AM Garfield Lewis 
wrote:

> The following knows there is no CTID so shouldn’t I be able to get
> something similar programmatically?
>
> [sysprog@nucky lz_pgmod] (h-master-LZRDB-4714)*$ psql -U postgres -d
> postgres -c "select ctid, 'test'"
> ERROR:  column "ctid" does not exist
> LINE 1: select ctid, 'test'
>

All that shows is that a column named ctid is not presently in scope (the
absence of a table and use of a name for the column guarantees that).  That
"ctid" and the system column named "ctid" are utterly unrelated; they just
happen to share the same character sequence.

Data values do not have any concept of their surrounding context.  They are
just, basically, POJO or JSON Objects.  They are created and passed around
never caring how they were born or how they might die, they just live in
the moment.

That is how it is and I suspect no amount of arguing would convince us to
complicate things.

David J.

Re: psql does not provide proper response

[Bryn Llewellyn]

> shishaozh...@gmail.com wrote:
> 
> I do not know what happened.
> 
> psql does not provide proper response anymore.
> 
> I typed the following and see nothing.
> 
> user=# select * from boundaryline.scotland_and_wales_const_region
> user-#
> 
> Can anyone enlighten me?

This happens to me all too frequently. Then I kick myself and realize that I 
earlier said “\o spool.txt” (by hand) and forgot to turn it off with the bare 
“\o”. This is my typical use case:

\o spool.txt
\i script.sql

The script causes an error. I read it, see the bad statement, and then type the 
correct statement by hand. Then I see what you did: a big fat nothing.

Might this be happening to you?

Re: [EXT] Re: Can we get the CTID value

[Garfield Lewis]

On 2022-01-20, 1:11 PM, "Tom Lane"  wrote:

>No, it's the same problem in reverse: the output function cannot
>know where the value came from.  There is no hard and fast
>reason that it must have come out of a table, either.  Consider
>something as simple as
>
>   SELECT 'blah blah'::yourtype;
>
>This'll invoke the type's input function to parse the literal string,
>and later it'll invoke the output function to reconstruct a string
>
>to send to the client, and there's no table involved.
>
>   regards, tom lane

Understood, however, my last question/comment would be shouldn't the example 
above just result in a CTID something like (x,y) where x and y are some known 
UNKNOWN/INVALID values or something else representing the fact that there is no 
current CTID associated with the element? Basically, what I am saying is 
shouldn't any search for a CTID in the case just return some value to indicate 
the CTID doesn't exist or is UNKNOWN/INVALID?

The following knows there is no CTID so shouldn’t I be able to get something 
similar programmatically?

[sysprog@nucky lz_pgmod] (h-master-LZRDB-4714)*$ psql -U postgres -d postgres 
-c "select ctid, 'test'"
ERROR:  column "ctid" does not exist
LINE 1: select ctid, 'test'
   ^

Regards,
Garfield

Re: [EXT] Re: Can we get the CTID value

[Tom Lane]

Garfield Lewis  writes:
> I think you are right in the case of INPUT/RECEIVE, however we should be able 
> to get that info during OUTPUT/SEND (I think) since it is fixed at that 
> point. At the time I return the information to the user I could augment the 
> output to add that information to the output. However, I still don't know if 
> it is even possible to get that information in those functions. Is that at 
> all possible?

No, it's the same problem in reverse: the output function cannot
know where the value came from.  There is no hard and fast
reason that it must have come out of a table, either.  Consider
something as simple as

SELECT 'blah blah'::yourtype;

This'll invoke the type's input function to parse the literal string,
and later it'll invoke the output function to reconstruct a string
to send to the client, and there's no table involved.

regards, tom lane

Re: [EXT] Re: Can we get the CTID value

[Garfield Lewis]

> On 2022-01-20, 12:52 PM, "Tom Lane"  wrote:
>
>Garfield Lewis  writes:
>> I need the page and possibly row of the data location to be stored as an 
> element of the new type. This is to simulate a structure from another 
> database system.
>
>You need to rethink.  The datatype input function cannot know even that
>the value is going to be stored anywhere, let alone exactly where.
>Moreover, what would happen if the row is moved somewhere else due
>to an update of some other column?
>
>You might be able to build something for cross-linking by putting
>the logic in AFTER INSERT/UPDATE/DELETE triggers, but I think a
>custom datatype is not going to be helpful for that.
>
>   regards, tom lane

Thx, Tom...

I think you are right in the case of INPUT/RECEIVE, however we should be able 
to get that info during OUTPUT/SEND (I think) since it is fixed at that point. 
At the time I return the information to the user I could augment the output to 
add that information to the output. However, I still don't know if it is even 
possible to get that information in those functions. Is that at all possible?

Regards,
Garfield

Re: Query much slower from php than psql or dbeaver

[Tom Lane]

Ekaterina Amez  writes:
> I've tested the query with psql and DBeaver and it takes only milliseconds:
> it returns 39 records and now there's only 16000 records on the table but
> I've tested it with <100K. When I've tested my php script the same query
> takes 14 minutes to return (more or less).

A plausible theory is that it's not really the same query, but differs
in having some values presented as parameters not literal constants.
Depending on exactly how you do it, that can confuse the planner
leading to a poor execution plan and a long runtime.

I'd first try enabling log_statement on the server to see if the
query is really being presented exactly the same way.  Another
thing worth trying is auto_explain, to capture the plans actually
being used.

regards, tom lane

Re: psql does not provide proper response

[Rob Sargent]

On 1/20/22 10:54, Shaozhong SHI wrote:

I do not know what happened.

psql does not provide proper response anymore.

I typed the following and see nothing.

user=# select * from boundaryline.scotland_and_wales_const_region
user-#

Can anyone enlighten me?

Regards,

David

Add semi-colon return?

psql does not provide proper response

[Shaozhong SHI]

I do not know what happened.

psql does not provide proper response anymore.

I typed the following and see nothing.

user=# select * from boundaryline.scotland_and_wales_const_region
user-#

Can anyone enlighten me?

Regards,

David

Re: [EXT] Re: Can we get the CTID value

[Tom Lane]

Garfield Lewis  writes:
> I need the page and possibly row of the data location to be stored as an 
> element of the new type. This is to simulate a structure from another 
> database system.

You need to rethink.  The datatype input function cannot know even that
the value is going to be stored anywhere, let alone exactly where.
Moreover, what would happen if the row is moved somewhere else due
to an update of some other column?

You might be able to build something for cross-linking by putting
the logic in AFTER INSERT/UPDATE/DELETE triggers, but I think a
custom datatype is not going to be helpful for that.

regards, tom lane

Re: Query on postgres_fdw extension

[Vijaykumar Jain]

On Thu, 20 Jan 2022 at 21:29, Duarte Carreira  wrote:

> Hello everyone.
>
> I don't know... realistically what do you guys see as a best/simple
> approach?
>

We implemented a custom sharding (directory sharding with lookup tables)
layer of 10 shards, but it was write local, read global.
the api was responsible for all rebalancing incase of hotspots.
other api sharding examples ...
Database Sharding: Solving Performance in a  Multi-Tenant Restaurant Data
Analytics System (gotenzo.com)



although
it worked really well, when you are maintaining it on your own, it gets
really painful, much beyond id generation globally.

i will not go into the details, but in short, sharded setup is not the same
as local setup. there would be many more things that would not work as
expected
which would otherwise work really well on a standalone setup.

writes over shard may work, but you realize it is over the network, so you
can lock you table for a much longer duration and cause a much more serious
outage,
if you really wanted to have distributed writes with unique keys, you can
go with uuid i think or have your own seq generator globally (see below).


*Move ID generation out of the database to an ID generation service outside
of the database… As soon as a piece of work enters their system, an ID gets
assigned to it… and that ID generated in a way that is known to be globally
unique within their system*

A Better ID Generator For PostgreSQL | robconery

Index of /shard_manager/shard_manager-0.0.1/ (pgxn.org)
  (pretty old
but if you can use your coordinator server as a id_generator(), then you
can generate ids which are globally unique)
Sharding & IDs at Instagram. With more than 25 photos and 90 likes… | by
Instagram Engineering | Instagram Engineering (instagram-engineering.com)


imho, do not try sharding manually, unless you have enough dbas to maintain
the shards, try using citus, it would make a lot of the manual stuff easier.

also, the below work arounds are bad, incase you just want to rush through

postgres=# \c localdb
You are now connected to database "localdb" as user "postgres".
localdb=#
localdb=# \dt
Did not find any relations.
localdb=# \det
 List of foreign tables
 Schema | Table |Server
+---+---
 public | t | remote_server
(1 row)

localdb=# \det+ t
List of foreign tables
 Schema | Table |Server |  FDW options   |
Description
+---+---++-
 public | t | remote_server | (schema_name 'public', table_name 't') |
(1 row)

localdb=# \det t
 List of foreign tables
 Schema | Table |Server
+---+---
 public | t | remote_server
(1 row)

localdb=# create or replace function getnext() returns int as $_$ select id
FROM dblink ('dbname = remotedb', $$ select nextval('t_id_seq') $$  ) as
newtable(id int); $_$ language sql;
CREATE FUNCTION
localdb=# \c remotedb
You are now connected to database "remotedb" as user "postgres".
remotedb=# \dt t
List of relations
 Schema | Name | Type  |  Owner
+--+---+--
 public | t| table | postgres
(1 row)

remotedb=# \ds t_id_seq
List of relations
 Schema |   Name   |   Type   |  Owner
+--+--+--
 public | t_id_seq | sequence | postgres
(1 row)

remotedb=# \c localdb
You are now connected to database "localdb" as user "postgres".
localdb=# insert into t values (getnext(), 100);
INSERT 0 1
localdb=# insert into t values (getnext(), 100);
INSERT 0 1
localdb=# select * from t;
 id | col1
+--
 11 |4
 12 |5
 13 |  100
 14 |  100
(4 rows)

just my opinion, ignore it not useful.

Re: [EXT] Re: Can we get the CTID value

[Garfield Lewis]

A CTID is a special column documented here: 
https://www.postgresql.org/docs/12/ddl-system-columns.html

Regards,
Garfield

Re: [EXT] Re: Can we get the CTID value

[Garfield Lewis]

Hi Laurenz,

I need the page and possibly row of the data location to be stored as an 
element of the new type. This is to simulate a structure from another database 
system.

Regards,
Garfield

Re: Multiple SELECT statements Using One WITH statement

[David G. Johnston]

On Thu, Jan 20, 2022 at 7:42 AM Avi Weinberg  wrote:

>
>
> Thanks David for the reply, but my question was a little different.
>
> I know I can have multiple CTE queries like you showed, but I want to
> have one single WITH query, and use it in multiple queries, not just by one
> query the directly proceed the CTE.
>
> Why do I need to execute the CTE query twice if I have two queries that
> wants to use it?
>
>
>
Sorry, that was a bit of a drive-by for me.  I figured you could easily
test whether your proposed query structure would work and figured maybe you
didn't realize that CTEs could be chained together.

The short answer is that a query can only output a single result set so
having two top-level select statements is simply prohibited.  And result
sets are not cached between statements so it isn't like there would be any
place to store intermediate CTE results automatically.  As you've been
told, you can do that with temporary tables (it's a much bigger pain if you
want something that isn't session-local).

You can always write:

CREATE VIEW cte_view AS
WITH cte AS (...)
SELECT * FROM cte;

And then incorporate that into any queries that require the results of said
CTE.

David J.

Re: Query on postgres_fdw extension

[Duarte Carreira]

Hello everyone.

I got here after encountering the same difficulty, although on a much more
mundane scenario.

I'm used to fdw on a read-only basis. I was just inserting a new record on
a foreign table and got blocked... and after much searching got here.

Not to rant or anything, but I am completely surprised by this limitation.
As far as I can see it is impossible to use fdw to insert records on 99% of
tables, since all have some kind of primary sequential key.

I'm just a user so cannot really understand the intricacies involved in
this process. Tried to find past messages and up to 2013 without
understanding the real problem.

The simplest workaround seems to be to quit using auto-numbering mechanisms
and implement numbering trigger functions, which is really just going back
to the 90s...

Another option would be a local function that would get the remote default
and use it in a local insert trigger. The complexity is just orders of
magnitude higher. We are talking about auto-numbering keys...

I don't know... realistically what do you guys see as a best/simple
approach?

Having 2 tables seems to me the easiest, less complex solution, but it's
hard on maintenance...

And don't take this the wrong way, but is it really that hard to have a
compromise: if there's a serial on the remote, then the user could change
the local definition so to just send the "DEFAULT" keyword to the remote
and let it figure it out? At least the user would have a chance of setting
the preferred behavior without much fuss, on a per-table basis. And still
use the basic functionality of serial/identity columns. PostgreSQL has such
complex stuff that this seems odd to be left out.

Well I hope I didn't cross over as negative or anything. I do love pgsql
and always promote it as the best thing under the sun.

Best regards,
Duarte

Laurenz Albe  escreveu no dia quinta, 20/01/2022
à(s) 15:36:

> On Fri, 2021-05-14 at 10:53 -0400, Tom Lane wrote:
> > Swathi P  writes:
> > > Hence we decided to have the coordinator nodes as stateless and hence
> > > declared the column with no serial/sequence. Let me know if this makes
> > > sense.
> >
> > Attaching serial-sequence defaults on both sides would certainly not
> > work very well, because the sequences wouldn't stay in sync.
> >
> > Unfortunately, postgres_fdw just doesn't have a good way right now
> > to make use of dynamically-generated defaults at the remote server.
> > If you leave out a column in your INSERT, it's going to compute
> > and send the locally-defined default (which is just null in this
> > case), so the remote's default expression is never used.
> >
> > I remember that we spent a great deal of effort in postgres_fdw's
> > early days, trying to find a way that we could use the remote's
> > defaults in cases like this.  But everything we tried ended up
> > causing horrible semantic inconsistencies, so we ended up with
> > the always-use-the-local-default approach.  There was some feeling
> > that maybe this could be revisited later, but no one's done so.
> >
> > One conceivable workaround is to do your insertions through a
> > foreign table that doesn't even have the serial column, so that
> > the INSERT command received by the remote server lacks that
> > column and the default gets applied.  Probably too messy though.
>
> One possibility might be to define a trigger on the remote table
> that fetches the next sequence value if you try to insert NULL.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>
>
>

Re: Multiple SELECT statements Using One WITH statement

[Johannes Graën]

On 20/01/2022 15.42, Avi Weinberg wrote:

Thanks David for the reply, but my question was a little different.

I know I can have multiple CTE queries like you showed, but I want to 
have one single WITH query, and use it in multiple queries, not just by 
one query the directly proceed the CTE.


Why do I need to execute the CTE query twice if I have two queries that 
wants to use it?


When data is going to be provided to an application via a REST API, I 
find this pattern quite useful:


WITH list AS (
SELECT generate_series(1,10) AS n
)
SELECT json_build_object(
'even', (
SELECT json_agg(n)
FROM (
SELECT n
FROM list
WHERE n%2 = 0
) even
),
'odd', (
SELECT json_agg(n)
FROM (
SELECT n
FROM list
WHERE n%2 = 1
) odd
)
) obj;


If data is to be written to separate tables, writing the intermediate 
result to a temporary table as explained by Josef might be the simplest 
solution.

Re: Multiple SELECT statements Using One WITH statement

[Josef Šimánek]

čt 20. 1. 2022 v 13:48 odesílatel Avi Weinberg  napsal:
>
> Hi,
Hello!
>
>
> Can I have multiple select statements using one WITH statement?
>
>
>
> WITH t AS (
>
> Select A, B from …
>
> )
>
> SELECT A into tableA FROM t where ….;
>
>
>
> SELECT B into tableB FROM t where ….;
>

I think it is not possible that way. Instead you can "cache" the query
result into a temporary table. CREATE TEMPORARY TABLE  ... ON COMMIT
DROP could be useful here. There is simple example mentioned in this
(https://stackoverflow.com/a/52384424/319233) stackoverflow answer.
>
> IMPORTANT - This email and any attachments is intended for the above named 
> addressee(s), and may contain information which is confidential or 
> privileged. If you are not the intended recipient, please inform the sender 
> immediately and delete this email: you should not copy or use this e-mail for 
> any purpose nor disclose its contents to any person.

RE: Multiple SELECT statements Using One WITH statement

[Avi Weinberg]

Thanks David for the reply, but my question was a little different.
I know I can have multiple CTE queries like you showed, but I want to have one 
single WITH query, and use it in multiple queries, not just by one query the 
directly proceed the CTE.
Why do I need to execute the CTE query twice if I have two queries that wants 
to use it?

Thanks

From: David G. Johnston [mailto:david.g.johns...@gmail.com]
Sent: Thursday, January 20, 2022 2:58 PM
To: Avi Weinberg 
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Multiple SELECT statements Using One WITH statement



On Thursday, January 20, 2022, Avi Weinberg 
mailto:a...@gilat.com>> wrote:
Hi,

Can I have multiple select statements using one WITH statement?

WITH t AS (
Select A, B from …
)
SELECT A into tableA FROM t where ….;

SELECT B into tableB FROM t where ….;

With q1 as (), q2 as (), q3 as () main_query

David J.

IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.

Query much slower from php than psql or dbeaver

[Ekaterina Amez]

Hi,

After receiving an Unknown Address error with *pgsql-...@postgresql.org* I've
discovered this mailing list is catalogued as Inactive, so I'm sending my
question to this list.

I've made a php cli script that downloads a file from FTP, loads it in a
table and compares against the same table in 2 different databases that are
connected to the main one with pgsql foreign data wrapper (so there are 3
DB involved via linked servers).

I've tested the query with psql and DBeaver and it takes only milliseconds:
it returns 39 records and now there's only 16000 records on the table but
I've tested it with <100K. When I've tested my php script the same query
takes 14 minutes to return (more or less). I've checked there are no
blocking processes while runnig this query, I've analyzed the table,
dropped and recreated, added indexes... and every time I run my script it
takes those 14 minutes.

The script opens the connection and after that makes all of the operations:
some checks, load the file with COPY sentence, move data with some
additional columns to final table, and after this and without dropping the
connection it runs this query. After this long query, script iterates over
the returned records to make some other queries for every record. And
finally there are 2 more queries, that makes the reverse search: the slow
query checks diferences or non-existing records from file data in the 2
linked databases, and the las 2 queries check non-existing records from
their tables in the file. Everything goes smooth except this long-lasting
query.

This is the query:
select t1. as maindb_, t1.bbb as maindb_bbb, t1.c as
maindb_c, t1.timestamp_create as maindb_create,
t1.timestamp_closed as maindb_close, t1.d as maindb_d,
null::text as db1_sth,
t2. as db1_, t2. as db1_, null::text as
db2_sth,
t3. as db2_, t3. as db2_
from table1 t1
left join database1_fdw.table2 t2 on t1. = t2.btatpd_
and t1.file = 'file_name.csv'
and t2.btatpd_fecha = '2022011912'
and substring(t1.bbb from 1 for 3) in ()
left join database2_fdw.table2 t3 on t1. = t3.btatpd_
and t1.fichero_origen = 'file_name.csv'
and t3.btatpd_fecha = '2022011912'
and substring(t1.bbb from 1 for 3) in ( t2.)
or
(t3. is not null and t1.d <> t3.)
)
order by t1.bbb nulls last;

I'm using PHP 5.3.3, with PG 9.6 over CentOS 6.8. I'm connecting to PG with
pg_connect and pg_query because here nobody is using PDO. And I have to
warn you that I'm not a PHP expert. Also I'm aware how old these versions
are, I'm trying to finish things to start with server migration.

This is the explain plan, in case it gives more info:
Sort  (cost=5345.39..5385.35 rows=15986 width=212) (actual
time=112.315..112.318 rows=39 loops=1)
  Output: t1., t1.bbb, t1.c, t1.timestamp_create,
t1.timestamp_closed, t1.d, NULL::text, t2., t2.,
NULL::text, t3., t3.
  Sort Key: t1.bbb
  Sort Method: quicksort  Memory: 29kB
  Buffers: shared hit=1255
  ->  Hash Left Join  (cost=237.54..2587.70 rows=15986 width=212) (actual
time=81.272..112.248 rows=39 loops=1)
Output: t1., t1.bbb, t1.c, t1.timestamp_create,
t1.timestamp_closed, t1.d, NULL::text, t2., t2.,
NULL::text, t3., t3.
Hash Cond: ((t1.)::text = (t3.btatpd_)::text)
Join Filter: (((t1.file)::text = 'file_name.csv'::text) AND
("substring"((t1.bbb)::text, 1, 3) = ANY ('{}'::text[])))
Rows Removed by Join Filter: 1
Filter: (((t2. IS NULL) AND (t3. IS NULL)) OR
((t2. IS NOT NULL) AND (t1.d <> t2.)) OR
((t3. IS NOT NULL) AND (t1.d <> t3.)))
Rows Removed by Filter: 15794
Buffers: shared hit=1252
->  Hash Left Join  (cost=118.77..2408.88 rows=15988 width=149)
(actual time=71.890..101.261 rows=15820 loops=1)
  Output: t1., t1.bbb, t1.c, t1.timestamp_create,
t1.timestamp_closed, t1.d, t1.file, t2., t2.
  Hash Cond: ((t1.)::text = (t2.btatpd_)::text)
  Join Filter: (((t1.file)::text = 'file_name.csv'::text) AND
("substring"((t1.bbb)::text, 1, 3) = ANY
('{}'::text[])))
  Buffers: shared hit=1252
  ->  Seq Scan on public.table1 t1  (cost=0.00..2230.06
rows=15988 width=103) (actual time=0.176..19.882 rows=15817 loops=1)
Output: t1.id, t1., t1.bbb, t1.c,
t1.timestamp_create, t1.timestamp_closed, t1.sbd_bundle_id, t1.d,
t1.file, t1.fecha_carga
Filter: (((t1.file)::text = 'file_name.csv'::text) AND
((t1.c)::text = 'ACTIVE'::text))
Rows Removed by Filter: 49387
Buffers: shared hit=1252
  ->  Hash  (cost=118.73..118.73 rows=3 width=94) (actual

Re: Cannot find hstore operator

[Tom Lane]

Paul van der Linden  writes:
> during maintenance I saw a lot of lines in my postgreslog saying:
> CONTEXT:  SQL function "line_function" during inlining
> automatic analyze of table "osm.planet_osm_line"
> ERROR:  operator does not exist: public.hstore -> unknown at character 45

It sounds like line_function is careless about its search path
assumptions.  auto-analyze will run index expressions with the
search_path set to empty (i.e., only pg_catalog is accessible)
and hstore isn't normally installed in pg_catalog.

The easy fix would be to attach "SET search_path = public"
to that function, but I believe that destroys the ability to
inline it, which might be a performance problem for you.
Alternatively you could schema-qualify the operator name,
that is "foo OPERATOR(public.->) bar".

regards, tom lane

Re: Multiple SELECT statements Using One WITH statement

[David G. Johnston]

On Thursday, January 20, 2022, Avi Weinberg  wrote:

> Hi,
>
>
>
> Can I have multiple select statements using one WITH statement?
>
>
>
> WITH t AS (
>
> Select A, B from …
>
> )
>
> SELECT A into tableA FROM t where ….;
>
>
>
> SELECT B into tableB FROM t where ….;
>
>
> With q1 as (), q2 as (), q3 as () main_query

David J.

Multiple SELECT statements Using One WITH statement

[Avi Weinberg]

Hi,

Can I have multiple select statements using one WITH statement?

WITH t AS (
Select A, B from ...
)
SELECT A into tableA FROM t where ;

SELECT B into tableB FROM t where ;

IMPORTANT - This email and any attachments is intended for the above named 
addressee(s), and may contain information which is confidential or privileged. 
If you are not the intended recipient, please inform the sender immediately and 
delete this email: you should not copy or use this e-mail for any purpose nor 
disclose its contents to any person.

Re: Can we get the CTID value

[o1bigtenor]

On Wed, Jan 19, 2022 at 1:39 PM Garfield Lewis
 wrote:
>
> Hi,
>
>
>
> I am creating a new type and would like to know if it was possible to access 
> the CTID for the row affected by the INPUT and RECEIVE functions of the new 
> type? Actually, would it be possible from the OUTPUT and SEND functions as 
> well?
>
>
Please - - - what is CTID?

TIA

Cannot find hstore operator

[Paul van der Linden]

Hi,

during maintenance I saw a lot of lines in my postgreslog saying:
CONTEXT:  SQL function "line_function" during inlining
automatic analyze of table "osm.planet_osm_line"
ERROR:  operator does not exist: public.hstore -> unknown at character 45
HINT:  No operator matches the given name and argument types. You might
need to add explicit type casts.

Now the hint gives me an option but I really don't like the sprinkling of
::text in all my functions
When executed (with search_path=public) this function works correctly, and
I doublechecked that all (or at least a lot of them) hstore related
functions are present in the public schema.
Are there any other solutions to this?

Paul

PS please cc me when answering

Re: Can we get the CTID value

[Laurenz Albe]

On Wed, 2022-01-19 at 19:38 +, Garfield Lewis wrote:
> I am creating a new type and would like to know if it was possible to access
> the CTID for the row affected by the INPUT and RECEIVE functions of the new 
> type?
> Actually, would it be possible from the OUTPUT and SEND functions as well?

That sounds confusing.  What are you trying to achieve?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com