RECURSIVE allowed only ONCE in a CTE

[Jitendra Loyal]

I find that the RECURSIVE can be used only once in a CTE.

I have the following use-case where there is a hierarchy of store_groups,
and then there are stores associated with a store_group. Requirement is to
ensure that a store can be used only once in a store group hierarchy.
Following definitions help:

CREATE TABLE store_groups
(
store_group_id SERIAL NOT NULL CONSTRAINT StoreGroups_PK_StoreGroupID
PRIMARY KEY,
store_group_nm STXT NOT NULL,
-- On update, parent_store_group_id should not exist in the sub-tree of
store_group_id (if any). This is to prevent cycles. Trigger ensures the
same.
-- Another trigger ensures that the stores are unique in the new store
group hierarchy.
parent_store_group_id INTEGER NULL CONSTRAINT
StoreGroups_FK_ParentStoreGroupID
REFERENCES store_groups,
CONSTRAINT StoreGroups_UK_ParentStoreGroupID_StoreGroupName
UNIQUE ( parent_store_group_id, store_group_nm )
);

CREATE TABLE store_group_stores
(
store_group_store_id SERIAL NOT NULL CONSTRAINT StoreGroupStores_PK_StoreID
PRIMARY KEY,
store_group_id INTEGER NOT NULL CONSTRAINT StoreGroupStores_FK_StoreGroupID
REFERENCES store_groups,
-- Trigger ensures that a store exists only once in a Group hierarchy
store_id INTEGER NOT NULL -- CONSTRAINT StoreGroupStores_FK_StoreID
-- REFERENCES stores,
-- Display order of the store in the Store Group
-- If display_order is not specified, stores should be listed in
alphabetical order
store_seq INTEGER NULL,
CONSTRAINT StoreGroupStores_UK_StoreGroupID_StoreID
UNIQUE ( store_group_id, store_id )
);

To meet the above need, I was writing a funciton (which will be called from
triigers) with following algorithm:

   1. root_group CTE to get the root store group (needs RECURSIVE)
   2. all_groups to collect all the store groups in root_group (needs
   RECURSIVE)

On the second use, I get syntax error.  Kindly confirm that RECURSIVE can
be used only once. I will find an alternate mechanism.

Thanks,
Jitendra Loyal

Re: RECURSIVE allowed only ONCE in a CTE

[Andrew Gierth]

> "Jitendra" == Jitendra Loyal  writes:

 Jitendra> I find that the RECURSIVE can be used only once in a CTE.

RECURSIVE can be specified only once, but it applies to all CTEs at that
level. That is to say, RECURSIVE qualifies the preceding WITH, _not_ the
following CTE.

Note that just specifying RECURSIVE doesn't mean that any CTE is
recursive, it simply changes the name scoping rules such that CTEs _can_
be recursive. (Without it, a CTE's name is not in scope in its own body
so recursion is impossible.)

-- 
Andrew (irc:RhodiumToad)

Re: RECURSIVE allowed only ONCE in a CTE

[mariusz]

On Fri, 22 Feb 2019 13:23:11 +0530
Jitendra Loyal  wrote:

> I find that the RECURSIVE can be used only once in a CTE.
> 
> I have the following use-case where there is a hierarchy of
> store_groups, and then there are stores associated with a
> store_group. Requirement is to ensure that a store can be used only
> once in a store group hierarchy. Following definitions help:
> 
> CREATE TABLE store_groups
> (
> store_group_id SERIAL NOT NULL CONSTRAINT StoreGroups_PK_StoreGroupID
> PRIMARY KEY,
> store_group_nm STXT NOT NULL,
> -- On update, parent_store_group_id should not exist in the sub-tree
> of store_group_id (if any). This is to prevent cycles. Trigger
> ensures the same.
> -- Another trigger ensures that the stores are unique in the new store
> group hierarchy.
> parent_store_group_id INTEGER NULL CONSTRAINT
> StoreGroups_FK_ParentStoreGroupID
> REFERENCES store_groups,
> CONSTRAINT StoreGroups_UK_ParentStoreGroupID_StoreGroupName
> UNIQUE ( parent_store_group_id, store_group_nm )
> );
> 
> CREATE TABLE store_group_stores
> (
> store_group_store_id SERIAL NOT NULL CONSTRAINT
> StoreGroupStores_PK_StoreID PRIMARY KEY,
> store_group_id INTEGER NOT NULL CONSTRAINT
> StoreGroupStores_FK_StoreGroupID REFERENCES store_groups,
> -- Trigger ensures that a store exists only once in a Group hierarchy
> store_id INTEGER NOT NULL -- CONSTRAINT StoreGroupStores_FK_StoreID
> -- REFERENCES stores,
> -- Display order of the store in the Store Group
> -- If display_order is not specified, stores should be listed in
> alphabetical order
> store_seq INTEGER NULL,
> CONSTRAINT StoreGroupStores_UK_StoreGroupID_StoreID
> UNIQUE ( store_group_id, store_id )
> );
> 
> To meet the above need, I was writing a funciton (which will be
> called from triigers) with following algorithm:
> 
>1. root_group CTE to get the root store group (needs RECURSIVE)
>2. all_groups to collect all the store groups in root_group (needs
>RECURSIVE)
> 
> On the second use, I get syntax error.  Kindly confirm that RECURSIVE
> can be used only once. I will find an alternate mechanism.
> 
> Thanks,
> Jitendra Loyal

hi,

you CAN use multiple recursive ctes within one query, i've done this
many times without issues having two or three recursive ctes between
nonrecursive ones.

you probably assumed that you need RECURSIVE keyword applied to cte,
and got syntax errors with multiple RECURSIVE keywords.

what you do is declare WITH RECURSIVE (RECURSIVE added to WITH keyword)
stating that following ctes may contain recursive one (maybe more, and
the recursive one need not be the first one).

what makes cte a recursive one is it's content, that is union
referencing self-cte within from clause.

i admit that i haven't analyzed your store case to make even a simple
working example of what you need, neither i'm pasting any exapmles of
my own real queries (one i got open in terminal just now has about 400
lines, 8 cte, 3 of which are recursive, that would rather be counter
productive as a working example)

just wanted to assure you that multiple recursive ctes within one query
are possible and need only one RECURSIVE keyword appended to WITH
keyword.

regards, mariusz

Re: RECURSIVE allowed only ONCE in a CTE

[Jitendra Loyal]

Thanks Andrew

I will try this and revert; I was specifying RECURSIVE for the second CTE
as well.

Regards,
Jitendra

Re: How many billion rows of data I can store in PostgreSQL RDS.

[Samuel Teixeira Santos]

Hi all.


Taking advantage of the topic I would like to know the recommendations
about how to update to a newer Postgres version having all that amount of
data.

Anyone one could share your experience about?

Thanks in advance.

Regards,


Samuel

Re: RECURSIVE allowed only ONCE in a CTE

[Jitendra Loyal]

Thanks Got it

Regards,
Jitendra

On Fri 22 Feb, 2019, 4:03 PM mariusz  On Fri, 22 Feb 2019 13:23:11 +0530
> Jitendra Loyal  wrote:
>
> > I find that the RECURSIVE can be used only once in a CTE.
> >
> > I have the following use-case where there is a hierarchy of
> > store_groups, and then there are stores associated with a
> > store_group. Requirement is to ensure that a store can be used only
> > once in a store group hierarchy. Following definitions help:
> >
> > CREATE TABLE store_groups
> > (
> > store_group_id SERIAL NOT NULL CONSTRAINT StoreGroups_PK_StoreGroupID
> > PRIMARY KEY,
> > store_group_nm STXT NOT NULL,
> > -- On update, parent_store_group_id should not exist in the sub-tree
> > of store_group_id (if any). This is to prevent cycles. Trigger
> > ensures the same.
> > -- Another trigger ensures that the stores are unique in the new store
> > group hierarchy.
> > parent_store_group_id INTEGER NULL CONSTRAINT
> > StoreGroups_FK_ParentStoreGroupID
> > REFERENCES store_groups,
> > CONSTRAINT StoreGroups_UK_ParentStoreGroupID_StoreGroupName
> > UNIQUE ( parent_store_group_id, store_group_nm )
> > );
> >
> > CREATE TABLE store_group_stores
> > (
> > store_group_store_id SERIAL NOT NULL CONSTRAINT
> > StoreGroupStores_PK_StoreID PRIMARY KEY,
> > store_group_id INTEGER NOT NULL CONSTRAINT
> > StoreGroupStores_FK_StoreGroupID REFERENCES store_groups,
> > -- Trigger ensures that a store exists only once in a Group hierarchy
> > store_id INTEGER NOT NULL -- CONSTRAINT StoreGroupStores_FK_StoreID
> > -- REFERENCES stores,
> > -- Display order of the store in the Store Group
> > -- If display_order is not specified, stores should be listed in
> > alphabetical order
> > store_seq INTEGER NULL,
> > CONSTRAINT StoreGroupStores_UK_StoreGroupID_StoreID
> > UNIQUE ( store_group_id, store_id )
> > );
> >
> > To meet the above need, I was writing a funciton (which will be
> > called from triigers) with following algorithm:
> >
> >1. root_group CTE to get the root store group (needs RECURSIVE)
> >2. all_groups to collect all the store groups in root_group (needs
> >RECURSIVE)
> >
> > On the second use, I get syntax error.  Kindly confirm that RECURSIVE
> > can be used only once. I will find an alternate mechanism.
> >
> > Thanks,
> > Jitendra Loyal
>
> hi,
>
> you CAN use multiple recursive ctes within one query, i've done this
> many times without issues having two or three recursive ctes between
> nonrecursive ones.
>
> you probably assumed that you need RECURSIVE keyword applied to cte,
> and got syntax errors with multiple RECURSIVE keywords.
>
> what you do is declare WITH RECURSIVE (RECURSIVE added to WITH keyword)
> stating that following ctes may contain recursive one (maybe more, and
> the recursive one need not be the first one).
>
> what makes cte a recursive one is it's content, that is union
> referencing self-cte within from clause.
>
> i admit that i haven't analyzed your store case to make even a simple
> working example of what you need, neither i'm pasting any exapmles of
> my own real queries (one i got open in terminal just now has about 400
> lines, 8 cte, 3 of which are recursive, that would rather be counter
> productive as a working example)
>
> just wanted to assure you that multiple recursive ctes within one query
> are possible and need only one RECURSIVE keyword appended to WITH
> keyword.
>
> regards, mariusz
>

Re: How many billion rows of data I can store in PostgreSQL RDS.

[Samuel Teixeira Santos]

Just adding that my case it's not a Amazon RDS, it's common server, if I
can say like that...

Re: How many billion rows of data I can store in PostgreSQL RDS.

[github kran]

On Fri, Feb 22, 2019 at 5:48 AM Samuel Teixeira Santos 
wrote:

> Just adding that my case it's not a Amazon RDS, it's common server, if I
> can say like that...
>
Aplologies I missed the point to mention that this is a question to
PostgreSQL community. We are currently using PostgreSQL. (  Aurora Postgres
RDS). I want to know what postgresql can handle in terms of limitations.

Thanks.

Partition and Functions

[Leandro Guimarães]

Hi Everyone,
  I have a partitioned by period table scenario here where I need to
execute a query with a function in where clause.

  I'm not sure if this is the best approach to do that, but when I use the
functions, it scans all the tables instead only the desired one. If I put
the parameter hardcoded, it works fine.

  Any idea how could I solve this? Follow my query example:

SELECT customer_id,
   date_id,
   kpi AS kpi_value
FROM   schema1.table1
WHERE  date_id >= *To_char*(( current_date - interval '30' day ) :: DATE,
'MMDD')::INTEGER


Thanks!
Leandro Guimarães

Update does not move row across foreign partitions in v11

[Derek Hans]

I've set up 2 instances of PostgreSQL 11. On instance A, I created a table
with 2 local partitions and 2 partitions on instance B using foreign data
wrappers, following https://pgdash.io/blog/postgres-11-sharding.html.
Inserting rows into this table works as expected, with rows ending up in
the appropriate partition. However, updating those rows only moves them
across partitions in some of the situations:

   - From local partition to local partition
   - From local partition to foreign partition

Rows are not moved

   - From foreign partition to local partition
   - From foreign partition to foreign partition

Is this the expected behavior? Am I missing something or configured
something incorrectly?

Thanks,
Derek

Re: How many billion rows of data I can store in PostgreSQL RDS.

[Adrian Klaver]

On 2/22/19 4:46 AM, github kran wrote:



On Fri, Feb 22, 2019 at 5:48 AM Samuel Teixeira Santos 
mailto:arcano...@gmail.com>> wrote:


Just adding that my case it's not a Amazon RDS, it's common server,
if I can say like that...

Aplologies I missed the point to mention that this is a question to 
PostgreSQL community. We are currently using PostgreSQL. (  Aurora 
Postgres RDS). I want to know what postgresql can handle in terms of 
limitations.


Aurora Postgres is a fork of the community version:

https://aws.amazon.com/rds/aurora/

"Amazon Aurora is a MySQL and PostgreSQL-compatible relational database 
built for the cloud, ..."


You will need to ask the folks that created the fork(AWS) what it's 
capabilities are.




Thanks.



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

streaming replication authentication

[Zachary Hanson-Hart]

Hi pgsql-general,

   I know that the requirement for replication is that a user be allowed to
connect to the "replication" database.  My question is how to configure the
streaming replication client to use a particular authentication method.  I
haven't been able to find examples of streaming replication using any
authentication other than trust or md5.

Specifically, I'm interested in using kerberos (via gssapi) with a keytab.
I'm able to authenticate from the command line with gss after doing kinit,
but that's not practical for replication.  Can gss authentication be used
in replication?

FWIW, I'm using RedHat 7's version of Postgresql 9.2.

Thanks for any hints or advice,

Zach

Re: PG Upgrade with hardlinks, when to start/stop master and replicas

[Martín Fernández]

On Fri, Feb 22, 2019 at 2:03 AM Tom Lane  wrote:

> Bruce Momjian  writes:
> > On Thu, Feb 21, 2019 at 09:31:32PM -0500, Stephen Frost wrote:
> >> * Bruce Momjian (br...@momjian.us) wrote:
> >>> There was too much concern that users would accidentally start the old
> >>> server at some later point, and its files would be hard linked to the
> >>> new live server, leading to disaster.
>
>
I think this is a great solution. Knowing that neither a human nor a
supervisor can mess up the hardlinks is something I really appreciate.


> >> Sure, I understand that concern, just wish there was a better approach
> >> we could use for "DO NOT START THIS SERVER" rather than moving of the
> >> pg_control file.
>
> > As ugly as it is, I have never heard of a better solution.
>
> system("rm -rf $OLDPGDATA") ... nah, that is not a better idea.
>
> regards, tom lane
>

Re: Running from 9.6 backups sometimes fails with fatal error

[Sergey Burladyan]

I started trying to debug backup with minimal WALs for achieve consistency, it 
is from 'server 2'
and it only needs 20 WAL files.

It error was:
ERROR:  xlog flush request 44B/7E5DAB28 is not satisfied --- flushed only to 
44B/7305B560
CONTEXT:  writing block 0 of relation base/16506/16891_vm

=== backup_label ===
START WAL LOCATION: 44B/6002F280 (file 0001044B0060)
CHECKPOINT LOCATION: 44B/7305B4F0
BACKUP METHOD: streamed
BACKUP FROM: standby
START TIME: 2019-02-13 03:19:54 MSK
LABEL: pg_basebackup base backup

=== controldata ===
Database cluster state:   in archive recovery
pg_control last modified: Wed 13 Feb 2019 03:19:54 AM MSK
Latest checkpoint location:   44B/7305B4F0
Latest checkpoint's REDO location:44B/6002F280
Latest checkpoint's REDO WAL file:0001044B0060
Time of latest checkpoint:Wed 13 Feb 2019 02:19:36 AM MSK
Minimum recovery ending location: 44B/7305B560

I scan all *_vm files in DB 16506 for pages with LSN bigger than 'Minimum 
recovery ending
location' and find three files:
base/16506/190024_vm 000 44B/9411F5C0
base/16506/190005_vm 000 44B/94121DE8
base/16506/16891_vm 000 44B/7E5DAB28

Then I parse (pg_xlogdump) all this 20 WALs, needed for consistency and search 
for this three
oid, for 190024 and 190005 I found VISIBLE WAL record:
=== $ grep 190024 wals-text | grep VISIBLE ===
rmgr: Heap2   len (rec/tot): 64/  8256, tx:  0, lsn: 
44B/651EDAE8, prev 44B/651EDA58, desc: VISIBLE cutoff xid 752732359 flags 1, 
blkref #0: rel 1663/16506/190024 fork vm blk 0 FPW, blkref #1: rel 
1663/16506/190024 blk 0
rmgr: Heap2   len (rec/tot): 59/59, tx:  0, lsn: 
44B/6B19A6E8, prev 44B/6B19A658, desc: VISIBLE cutoff xid 752734764 flags 1, 
blkref #0: rel 1663/16506/190024 fork vm blk 0, blkref #1: rel 
1663/16506/190024 blk 0
rmgr: Heap2   len (rec/tot): 59/59, tx:  0, lsn: 
44B/71194D40, prev 44B/71194CB0, desc: VISIBLE cutoff xid 752737874 flags 1, 
blkref #0: rel 1663/16506/190024 fork vm blk 0, blkref #1: rel 
1663/16506/190024 blk 0
=== $ grep 190005 wals-text | grep VISIBLE ===
rmgr: Heap2   len (rec/tot): 64/  8256, tx:  0, lsn: 
44B/651F3728, prev 44B/651F36C8, desc: VISIBLE cutoff xid 752732355 flags 1, 
blkref #0: rel 1663/16506/190005 fork vm blk 0 FPW, blkref #1: rel 
1663/16506/190005 blk 0
rmgr: Heap2   len (rec/tot): 59/59, tx:  0, lsn: 
44B/6B19AE80, prev 44B/6B19AE20, desc: VISIBLE cutoff xid 752732667 flags 1, 
blkref #0: rel 1663/16506/190005 fork vm blk 0, blkref #1: rel 
1663/16506/190005 blk 0
rmgr: Heap2   len (rec/tot): 59/59, tx:  0, lsn: 
44B/6B19CF10, prev 44B/6B19AEC0, desc: VISIBLE cutoff xid 752734752 flags 1, 
blkref #0: rel 1663/16506/190005 fork vm blk 0, blkref #1: rel 
1663/16506/190005 blk 1
rmgr: Heap2   len (rec/tot): 59/59, tx:  0, lsn: 
44B/711954D8, prev 44B/71195478, desc: VISIBLE cutoff xid 752737869 flags 1, 
blkref #0: rel 1663/16506/190005 fork vm blk 0, blkref #1: rel 
1663/16506/190005 blk 0

But I did not find in parsed WALs anything about vm fork for 16891.

Is it OK, or is something wrong here?

PS:
I try to remove all problematic visibility maps, with page LSN bigger than 
'Minimum recovery ending
location') and start backup without it. Without it postgres started 
successfully.

-- 
Sergey Burladyan

RE: pg_stat_statements doesn't track commit from pl/pgsql blocks

[legrand legrand]

forgot to cc pgsql-general list


De : legrand legrand 
Envoyé : vendredi 22 février 2019 20:26
À : Bruce Momjian
Objet : RE: pg_stat_statements doesn't track commit from pl/pgsql blocks

Hello Bruce,

thank you for taking time to answer.
yes, I was expecting something like

SELECT calls,query FROM pg_stat_statements;
 calls |   query
---+---
 1 | select pg_stat_statements_reset()
 1 | do $$ begin commit; end $$
 1 | commit

as I didn't found any other place to track this 'commit' information.

nb: I don't "want" anything this is just an open question,
and I'm perfectly able to ear that its not expected or not easy to implement.

Regards
PAscal

Re: Copy entire schema A to a different schema B

[Tiffany Thang]

Hi Melvin,
Unfortunately I was not able to use it because I was not able to access
pg_authid in RDS.

Thanks.

Tiff

On Thu, Feb 21, 2019 at 6:09 PM Melvin Davidson 
wrote:

> Tiffany, have you tried the clone_schema function? It seems to me it does
> exactly what you need, no dumping or restoring. There is
> even an option to copy the data or not. Default is not.
>
> On Thu, Feb 21, 2019 at 3:23 PM Adrian Klaver 
> wrote:
>
>> On 2/21/19 11:52 AM, Tiffany Thang wrote:
>> > Thanks everyone. Unfortunately the schema rename would not work since
>> > the source database will be our production system. We have not gone
>> live
>> > yet but the system is expected to be constantly used.
>> >
>> > I have multiple tables that I need to export ranging from 20GB to 60GB
>> > each. The parallel will not work for a single table but would be
>> > beneficial if I have multiple tables to dump.
>> >
>> > I'm thinking maybe using what Adrian has suggested with the -f option
>> > and then modify the file or maybe use a common public schema everywhere
>> > on the source and target databases. I would have to restrict who has
>> > access to the public schema.
>>
>> You can further break this down by using -s and -a switches to only work
>> with the table definitions and table data respectively. This can also be
>> done on the pg_dump end.
>>
>> >
>> > Thanks.
>> >
>> > Tiff
>> >
>>
>> >  >> --
>> >  >> Adrian Klaver
>> >  >> adrian.kla...@aklaver.com 
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: WSL (windows subsystem on linux) users will need to turn fsync off as of 11.2

[Thomas Munro]

On Tue, Feb 19, 2019 at 5:31 PM Thomas Munro  wrote:
> On Tue, Feb 19, 2019 at 5:16 PM James Sewell  
> wrote:
> >> Here's a starter patch that shows one of the approaches discussed.  It
> >> gets WSL users to a better place than they were before, by suppressing
> >> further warnings after the first one.
> >
> > This wasn't quite right, updated to check erro for ENOSYS (not rc)
> >
> > This compiles and stops the panic on WSL (with a single warning).
> >
> > I haven't tested if a version compiled on Linux will behave the same way - 
> > but based on the error messages in the top post it looks like the behavior 
> > is the same.
>
> Great.  Thanks for testing, and for the fix!  Well that all sounds
> like good news: it corrects the behaviour from 11.2, and also improves
> on the previous behaviour which I'd have accepted as a bug if anyone
> had reported it.  So the next problem is that we don't have a
> consensus on whether this is the right approach, so I don't feel like
> I can commit it yet.  Does any want to make another concrete proposal?

Ok, here's the version I'm planning to push soon if there are no objections.
Re-adding Bruce to the thread, as I just noticed the CC list got
pruned at some point in this thread.

-- 
Thomas Munro
https://enterprisedb.com


0001-Tolerate-ENOSYS-failure-from-sync_file_range-v3.patch
Description: Binary data

Re: streaming replication authentication

[Stephen Frost]

Greetings,

* Zachary Hanson-Hart (zac...@temple.edu) wrote:
>I know that the requirement for replication is that a user be allowed to
> connect to the "replication" database.  My question is how to configure the
> streaming replication client to use a particular authentication method.  I
> haven't been able to find examples of streaming replication using any
> authentication other than trust or md5.
> 
> Specifically, I'm interested in using kerberos (via gssapi) with a keytab.
> I'm able to authenticate from the command line with gss after doing kinit,
> but that's not practical for replication.  Can gss authentication be used
> in replication?

Yes, GSS auth can be used in replication.  Why do you think it's not
practical...?

> FWIW, I'm using RedHat 7's version of Postgresql 9.2.

PG 9.2 is no longer supported by this community, I strongly encourage
you to upgrade to a supported version.

Thanks!

Stephen


signature.asc
Description: PGP signature