Permission inconsistency with views that call functions

[David Wheeler]

Hi all

I’m seeing some inconsistency with how permissions are enforced within views. 
In particular, if the view accesses a table directly, then the table is 
accessible, however if the view uses a function to access the table then 
permission is denied.

Here’s a demonstration (from pg13.0)

createdb temp

psql temp <<\EOF
create schema hidden;

create table hidden.tab (col1 text);
insert into hidden.tab values ('abc');

create function test() returns text[] as $$
  select array_agg(col1) from hidden.tab;
$$ language sql stable;

create view tv1 as select test();
create view tv2 as select array_agg(col1) from hidden.tab;

grant select on tv1 to public;
grant select on tv2 to public;


create user test password 'test' login;
EOF



PGPASSWORD=test psql -U test temp <<\EOF
\echo select * from tv1;
select * from tv1;
\echo ---
\echo select * from tv2;
select * from tv2;
set jit_inline_above_cost to -1;
\echo ---
\echo select * from tv1; -- no jit inlining
select * from tv1;
EOF

OUTPUT

ERROR:  permission denied for schema hidden
LINE 2:   select array_agg(col1) from hidden.tab;
  ^
QUERY:
  select array_agg(col1) from hidden.tab;

CONTEXT:  SQL function "test" during inlining
---
select * from tv2;
array_agg
---
{abc}
(1 row)

SET
---
select * from tv1; -- no jit inlining
ERROR:  permission denied for schema hidden
LINE 2:   select array_agg(col1) from hidden.tab;
  ^
QUERY:
  select array_agg(col1) from hidden.tab;

CONTEXT:  SQL function "test" during inlining


Is this expected/desirable? Any ideas how I can work around it, short of 
inlining every function manually (if that’s even possible) or granting access 
to the “hidden” schema?

Thanks in advance!

Best regards,

David Wheeler, Inomial Architect
E. dwhee...@dgitsystems.com

313 La Trobe Street, Victoria 3000, Australia
+61 3 8820 5200

MELBOURNE . DENPASAR . AUCKLAND
WWW.DGITSYSTEMS.COM


[signature_414257395]

Re: fdatasync performance problem with large number of DB files

[Michael Brown]

On 2021-02-22 5:43 p.m., Tom Lane wrote:
> Michael Brown  writes:
>> * is there a knob missing we can configure?
> 
> No.  The trouble with sync() is that per POSIX, it only schedules the
> writes; there's no way to tell when the work has been done.  I see
> that Linux offers stronger promises in this department, but I don't
> think that's very portableTrue, but as mentioned below we're looking for a 
> "this makes sense for
our environment" switch.

> Moreover, even on Linux there's no way to detect whether any of the writes 
> failed.
Ugh. Presumably those would be noticed when the WAL replays? (I'll admit
I'd have to look at the sequence of events and think about it, I don't
know offhand.)

Oh, syncfs() exists but is Linux-specific, again, darn.

> Barring some solution to those problems, we would be unlikely to take
> a patch that uses sync() instead of fsync().
I wouldn't dare to propose outright switching to sync() for everyone,
but a knob we can turn on to say "use sync (or syncfd()) instead" is
what we need, discounting a better solution.

-- 
Michael Brown
Civilized Discourse Construction Kit, Inc.
https://www.discourse.org/

Re: Simple IN vs IN values performace

[Oleksandr Voytsekhovskyy]

Greetings,

Didn’t get your ideas with  materialized CTE or a subquery with OFFSET 0

Could you please show simple example?

> 23 февр. 2021 г., в 04:33, Michael Lewis  написал(а):
> 
> Insert the values into a TEMPORARY TABLE, then join that to your main table?
> 
> In my experience, this is very performant but needs an analyze command after 
> populating the temp table to ensure there are statistics so the plan doesn't 
> go awry. Otherwise, I'm not sure it is different from a materialized CTE or a 
> subquery with OFFSET 0 at the end.

Re: Simple IN vs IN values performace

[Alexander Voytsekhovskyy]

Thanks for suggestion

with tmp tables there are another issue - there are already 5-6 tables and
2-3 IN filters. If i will replace them with tmp tables it may hit query
planner limits and it will become to produce terrible query plans, for
example when genetic query optimizer starts

On Tue, Feb 23, 2021 at 1:45 AM Ron  wrote:

> On 2/22/21 9:00 AM, Oleksandr Voytsekhovskyy wrote:
>
> Greetings,
>
> We have queries with IN filters with long list of INT values
>
> Sometimes, they running extremely slow, and I have found suggestion to use
> syntax
>
> Field IN (VALUES(1465), (1478), ...
>
> Instead of
>
> Field IN (1465, 1478, ...
>
> On some cases it helps, but on other it makes query running 1000+ times
> slower
>
>
> Insert the values into a TEMPORARY TABLE, then join that to your main
> table?
>
>
> So the issue is:
> - some queries much faster with simple IN
> - some queries much faster with IN + VALUES
>
> Here is explain on case when it’s extremely slow:
>
> ->  HashAggregate  (cost=5.78..9.62 rows=385 width=4)
>Group Key: ""*VALUES*"".column1"
>->  Values Scan on ""*VALUES*""  (cost=0.00..4.81 rows=385 
> width=4)"
>
> What is the right way to pass long INT values list to IN filter?
>
> I am using PostgreSQL 13.1 on Ubuntu
>
>
> --
> Angular momentum makes the world go 'round.
>

Re: Simple IN vs IN values performace

[Pavel Stehule]

Hi

út 23. 2. 2021 v 10:36 odesílatel Alexander Voytsekhovskyy <
young.in...@gmail.com> napsal:

> Thanks for suggestion
>
> with tmp tables there are another issue - there are already 5-6 tables and
> 2-3 IN filters. If i will replace them with tmp tables it may hit query
> planner limits and it will become to produce terrible query plans, for
> example when genetic query optimizer starts
>

you can increase these limits - they are relatively low, and can be
increased on modern CPU.

https://www.postgresql.org/docs/current/runtime-config-query.html

Regards

Pavel


> On Tue, Feb 23, 2021 at 1:45 AM Ron  wrote:
>
>> On 2/22/21 9:00 AM, Oleksandr Voytsekhovskyy wrote:
>>
>> Greetings,
>>
>> We have queries with IN filters with long list of INT values
>>
>> Sometimes, they running extremely slow, and I have found suggestion to
>> use syntax
>>
>> Field IN (VALUES(1465), (1478), ...
>>
>> Instead of
>>
>> Field IN (1465, 1478, ...
>>
>> On some cases it helps, but on other it makes query running 1000+ times
>> slower
>>
>>
>> Insert the values into a TEMPORARY TABLE, then join that to your main
>> table?
>>
>>
>> So the issue is:
>> - some queries much faster with simple IN
>> - some queries much faster with IN + VALUES
>>
>> Here is explain on case when it’s extremely slow:
>>
>> ->  HashAggregate  (cost=5.78..9.62 rows=385 width=4)
>>Group Key: ""*VALUES*"".column1"
>>->  Values Scan on ""*VALUES*""  (cost=0.00..4.81 
>> rows=385 width=4)"
>>
>> What is the right way to pass long INT values list to IN filter?
>>
>> I am using PostgreSQL 13.1 on Ubuntu
>>
>>
>> --
>> Angular momentum makes the world go 'round.
>>
>

Re: Simple IN vs IN values performace

[Alexander Voytsekhovskyy]

Greetings,

i have tested both options ANY with string + parsing and simple array - and
there are cases when execution time 100+ times worse than IN

On Tue, Feb 23, 2021 at 12:23 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, February 22, 2021, Oleksandr Voytsekhovskyy 
> wrote:
>
>> What is the right way to pass long INT values list to IN filter
>>
>
> Don’t.
>
> Pass in a delimited string, then parse that string into an array and use
> “= any(array)”.
>
> This has the primary benefit of making the input a single parameter.
>
> David J.
>
>

Re: cannot promote after recovery for PITR

[Luca Ferrari]

On Mon, Feb 22, 2021 at 3:42 PM Luca Ferrari  wrote:
> If, instead, I do pg_wal_replay_resume(), the server is promoted (of
> course not at the PITR I want).
> Am I missing something?

Apparently I was missing caffeine and confused the usage of the functions.
pg_wal_replay_resume was what I was looking for.

Sorry for the noise.

Luca

Re: yum update for postgresql rpms

[Haas, Scott]

Tickling this thread to see if I can get a confirmation of the described 
behavior for rpm update via yum.


I do not want to cross-post (but if this is not the correct forum, please let 
me know).


Thanks,
Scott

From: Haas, Scott
Sent: Thursday, February 18, 2021 11:23 AM
To: pgsql-gene...@postgresql.org 
Subject: yum update for postgresql rpms

RHEL 7.9
PostgreSQL 12 (12.5.1 to 12.6.1)

I am updating RPMs for postgres and was interested to know whether this is 
indeed expected behavior for the rpm update:

When the "yum update" is executed, postgresql rpms are updated.  It looks at 
that point, postgresql (postmaster) processes automatically restart.  In the 
procedure I was building, I figured I'd need to stop/start postgres (systemctl 
stop postgresql-12, systemctl start postgresql-12), but that doesn't look to be 
necessary.

I don't recall this automatic postmaster restart occurring a few weeks ago when 
I went from 12.4.1 to 12.5.1 - perhaps it did, but I just stopped/re-started 
postgres unnecessarily.


Thanks,
Scott

--
Scott Haas
Enterprise Applications

sah...@psu.edu
814-863-3526
https://keybase.io/sah209

New operators and class for jsonb with gin indexing

[Luka Zivkovic]

Hello all!

I am having problems applying gin indexing to my new operator class for
type jsonb.
I created an operator that can query(jsonb column) keys and dates where
dates are more than, less than, equal and not equal to the one provided
inside another jsonb.
Because I want to make it use index search and not sequential search, I
wanted to use gin indexing, but it says that i'm missing support functions.
Did anybody try to do something like this? I searched everywhere online,
and I didn't see an example of how they should look like. This is how i
created an operator class

CREATE OPERATOR CLASS custom_jsonb_dates_operators FOR TYPE jsonb
USING gin  AS
OPERATOR 1 #>? (jsonb, jsonb),
OPERATOR 2 #

Re: yum update for postgresql rpms

[Ron]

It's standard procedure for package managers to stop daemons before update 
and then start them back up afterward.


On 2/23/21 8:22 AM, Haas, Scott wrote:
Tickling this thread to see if I can get a confirmation of the described 
behavior for rpm update via yum.



I do not want to cross-post (but if this is not the correct forum, please 
let me know).



Thanks,
Scott

*From:* Haas, Scott
*Sent:* Thursday, February 18, 2021 11:23 AM
*To:* pgsql-gene...@postgresql.org 
*Subject:* yum update for postgresql rpms
RHEL 7.9
PostgreSQL 12 (12.5.1 to 12.6.1)

I am updating RPMs for postgres and was interested to know whether this is 
indeed expected behavior for the rpm update:


When the "yum update" is executed, postgresql rpms are updated.  It looks 
at that point, postgresql (postmaster) processes automatically restart.  
In the procedure I was building, I figured I'd need to stop/start postgres 
(systemctl stop postgresql-12, systemctl start postgresql-12), but that 
doesn't look to be necessary.


I don't recall this automatic postmaster restart occurring a few weeks ago 
when I went from 12.4.1 to 12.5.1 - perhaps it did, but I just 
stopped/re-started postgres unnecessarily.



Thanks,
Scott

--
Scott Haas
Enterprise Applications
sah...@psu.edu 814-863-3526 https://keybase.io/sah209



--
Angular momentum makes the world go 'round.

Re: pg_restore - generated column - not populating

[Santosh Udupi]

The pg_restore command is actually  pg_restore -Ft -d mydb mydb.tar (my
mistake).

I didn't provide the -h -p -U since I use the super user account to restore
(I will try adding them). The restore had always worked until I altered the
table in the source database.

After I added the column, the restore still takes place but does not
populate the generated column. I did a backup using pgAdmin and the restore
populated all data using the same syntax on the tar file. So my
suspicion is that pg_dump is not doing the dump correctly. I will work on
it further. Thanks for your suggestions.

On Mon, Feb 22, 2021 at 9:23 PM Adrian Klaver 
wrote:

> On 2/22/21 7:43 PM, Santosh Udupi wrote:
> > If I backup using pgAdmin, I am able to restore using pg_restore but for
> > some reason, pg_rsestore on the output from pg_dump does not create
> > values for the generated columns
> >
>
> To troubleshoot this:
>
> 1) Stick to one dump/restore combination. The three versions you tried
> before just confuse the issue. For instance:
>
> pg_dump -Ft mydb > mydb.tar
> pg_restore -Ft -d mydb mydb.backup
>
> makes no sense. As mydb.backup came from:
>
> pg_dump -C -Fc mydb > mydb.backup
>
> I have not tested, but I'm pretty sure the pg_restore just ignored the
> -Ft and just did -Fc.
>
> 2) Big explicit in your dump and restore commands for -h(ost), -p(ort)
> and -U(ser). I suspect you may not be restoring to where you think you are.
>
> 3) Closely follow the progress of both the dump and the restore.
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: yum update for postgresql rpms

[Devrim Gündüz]

Hi,

On Thu, 2021-02-18 at 16:23 +, Haas, Scott wrote:
> RHEL 7.9
> PostgreSQL 12 (12.5.1 to 12.6.1)
> 
> I am updating RPMs for postgres and was interested to know whether
> this is indeed expected behavior for the rpm update:
> 
> When the "yum update" is executed, postgresql rpms are updated.  It
> looks at that point, postgresql (postmaster) processes automatically
> restart.  In the procedure I was building, I figured I'd need to
> stop/start postgres (systemctl stop postgresql-12, systemctl start
> postgresql-12), but that doesn't look to be necessary.
> 
> I don't recall this automatic postmaster restart occurring a few
> weeks ago when I went from 12.4.1 to 12.5.1 - perhaps it did, but I
> just stopped/re-started postgres unnecessarily.

That is the behaviour since 15 years :)

Regards,
-- 
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


signature.asc
Description: This is a digitally signed message part

Re: Custom base type and suboptimal query plans

[Tom Lane]

Ayo  writes:
> I have a database where I converted an integer primary key column to a
> custom base type that pretty much amounts to a wrapper around an
> integer, and now some queries are resulting in much slower query plans.
> Does Postgres have special optimizations for integers that are not
> available for custom types, or did I perhaps overlook something?

The slow query isn't using the chars_pkey1 index, which makes one
wonder if you have a corresponding index in the custom-type case,
or if you fat-fingered something about the index operator class
for the custom type.  As of v13 I don't think there's anything in
that area that custom types can't replicate ... but there certainly
is plenty of infrastructure for the standard types that you'll need
to build out if you want equivalent functionality.

regards, tom lane

Re: pg_restore - generated column - not populating

[Adrian Klaver]

On 2/23/21 6:36 AM, Santosh Udupi wrote:
The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my 
mistake).


I didn't provide the -h -p -U since I use the super user account to 
restore (I will try adding them). The restore had always worked until I 
altered the table in the source database.


After I added the column, the restore still takes place but does not 
populate the generated column. I did a backup using pgAdmin and the 
restore populated all data using the same syntax on the tar file. So my 
suspicion is that pg_dump is not doing the dump correctly. I will work 
on it further. Thanks for your suggestions.




pgAdmin uses pg_dump to do backups.


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

Re: New operators and class for jsonb with gin indexing

[Tom Lane]

Luka Zivkovic  writes:
> I am having problems applying gin indexing to my new operator class for
> type jsonb.
> I created an operator that can query(jsonb column) keys and dates where
> dates are more than, less than, equal and not equal to the one provided
> inside another jsonb.
> Because I want to make it use index search and not sequential search, I
> wanted to use gin indexing, but it says that i'm missing support functions.
> Did anybody try to do something like this?

There's not a whole lot of documentation about this, but there is some:

https://www.postgresql.org/docs/13/gin-extensibility.html

After reading that, I'd suggest looking into the code for gin opclass
support in various contrib modules (hstore might be a good start).

regards, tom lane

Re: pg_restore - generated column - not populating

[Santosh Udupi]

Got it. Must be the version difference. I run pgAdmin on Windows PC but
direct pg_dump on Ubuntu 20.04.

On Tue, Feb 23, 2021 at 7:27 AM Adrian Klaver 
wrote:

> On 2/23/21 6:36 AM, Santosh Udupi wrote:
> > The pg_restore command is actually pg_restore -Ft -d mydb mydb.tar (my
> > mistake).
> >
> > I didn't provide the -h -p -U since I use the super user account to
> > restore (I will try adding them). The restore had always worked until I
> > altered the table in the source database.
> >
> > After I added the column, the restore still takes place but does not
> > populate the generated column. I did a backup using pgAdmin and the
> > restore populated all data using the same syntax on the tar file. So my
> > suspicion is that pg_dump is not doing the dump correctly. I will work
> > on it further. Thanks for your suggestions.
> >
>
> pgAdmin uses pg_dump to do backups.
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: New operators and class for jsonb with gin indexing

[Luka Zivkovic]

Thank you for your response!

Yeah, i was looking i to that, and it looks like i just didn't implement
it correctly. I'm just wondering because I'm using it for jsonb type, can i
just use already implemented support functions for jsonb? I'm just not too
sure how those functions work

Thanks,
Luka Živković

On Tue, Feb 23, 2021, 16:28 Tom Lane  wrote:

> Luka Zivkovic  writes:
> > I am having problems applying gin indexing to my new operator class for
> > type jsonb.
> > I created an operator that can query(jsonb column) keys and dates where
> > dates are more than, less than, equal and not equal to the one provided
> > inside another jsonb.
> > Because I want to make it use index search and not sequential search, I
> > wanted to use gin indexing, but it says that i'm missing support
> functions.
> > Did anybody try to do something like this?
>
> There's not a whole lot of documentation about this, but there is some:
>
> https://www.postgresql.org/docs/13/gin-extensibility.html
>
> After reading that, I'd suggest looking into the code for gin opclass
> support in various contrib modules (hstore might be a good start).
>
> regards, tom lane
>

Re: Permission inconsistency with views that call functions

[Joe Conway]

On 2/22/21 10:32 PM, David Wheeler wrote:
> I’m seeing some inconsistency with how permissions are enforced within views. 
> In
> particular, if the view accesses a table directly, then the table is 
> accessible,
> however if the view uses a function to access the table then permission is 
> denied.

Without looking too closely at the details, I can almost guarantee that the
issue is that FUNCTIONs default to "SECURITY INVOKER" whereas VIEWs operate as
if they were "SECURITY DEFINER". See slide 33 here:

http://joeconway.com/presentations/security-pgcon2020.pdf

The solution to your issue is possibly to make the function "SECURITY DEFINER".

I have mused previously (not sure if I ever did on the lists, but in any case)
that it would be cool if VIEWs could have the option to be either DEFINER or
INVOKER so that VIEWs and FUNCTIONs could be treated the same, but no efforts
have been made in that direction as far as I am aware.

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: pg_restore - generated column - not populating

[Adrian Klaver]

On 2/23/21 7:39 AM, Santosh Udupi wrote:
Got it. Must be the version difference. I run pgAdmin on Windows PC but 
direct pg_dump on Ubuntu 20.04.


The OS does not really make a difference it is the pg_dump/restore 
versions and the Postgres server(s) versions that are important.




On Tue, Feb 23, 2021 at 7:27 AM Adrian Klaver > wrote:


On 2/23/21 6:36 AM, Santosh Udupi wrote:
 > The pg_restore command is actually pg_restore -Ft -d mydb
mydb.tar (my
 > mistake).
 >
 > I didn't provide the -h -p -U since I use the super user account to
 > restore (I will try adding them). The restore had always worked
until I
 > altered the table in the source database.
 >
 > After I added the column, the restore still takes place but does not
 > populate the generated column. I did a backup using pgAdmin and the
 > restore populated all data using the same syntax on the tar file.
So my
 > suspicion is that pg_dump is not doing the dump correctly. I will
work
 > on it further. Thanks for your suggestions.
 >

pgAdmin uses pg_dump to do backups.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: New operators and class for jsonb with gin indexing

[Tom Lane]

Luka Zivkovic  writes:
> Yeah, i was looking i to that, and it looks like i just didn't implement
> it correctly. I'm just wondering because I'm using it for jsonb type, can i
> just use already implemented support functions for jsonb? I'm just not too
> sure how those functions work

The support functions need to know about the operators' semantics, so
you have to write your own when inventing new operators.

regards, tom lane

Re: pg_restore - generated column - not populating

[Santosh Udupi]

Right. pg_dump under the pgAdmin runtime folder works perfectly.  pg_dump
in postgres13 (ubuntu) does not work. Exact same syntax.

When I try to restore, the backup that was taken using pgAdmin's version
restores properly but the one taken using postgres13's pg_dump, restores
the database but does not populate the generated columns. Strange!!

On Tue, Feb 23, 2021 at 7:50 AM Adrian Klaver 
wrote:

> On 2/23/21 7:39 AM, Santosh Udupi wrote:
> > Got it. Must be the version difference. I run pgAdmin on Windows PC but
> > direct pg_dump on Ubuntu 20.04.
>
> The OS does not really make a difference it is the pg_dump/restore
> versions and the Postgres server(s) versions that are important.
>
> >
> > On Tue, Feb 23, 2021 at 7:27 AM Adrian Klaver  > > wrote:
> >
> > On 2/23/21 6:36 AM, Santosh Udupi wrote:
> >  > The pg_restore command is actually pg_restore -Ft -d mydb
> > mydb.tar (my
> >  > mistake).
> >  >
> >  > I didn't provide the -h -p -U since I use the super user account
> to
> >  > restore (I will try adding them). The restore had always worked
> > until I
> >  > altered the table in the source database.
> >  >
> >  > After I added the column, the restore still takes place but does
> not
> >  > populate the generated column. I did a backup using pgAdmin and
> the
> >  > restore populated all data using the same syntax on the tar file.
> > So my
> >  > suspicion is that pg_dump is not doing the dump correctly. I will
> > work
> >  > on it further. Thanks for your suggestions.
> >  >
> >
> > pgAdmin uses pg_dump to do backups.
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Custom base type and suboptimal query plans

[Tom Lane]

Ayo  writes:
> On 2021-02-23, Tom Lane wrote:
>> The slow query isn't using the chars_pkey1 index, which makes one
>> wonder if you have a corresponding index in the custom-type case,

> The index exists and is usable in the custom-type case:

Hmm.  The next most likely theory seems to be something wrong with
cost estimation, causing the planner to avoid the nestloop-with-
inner-indexscan plan even though it would work.  Have you tried
"enable_seqscan = off" (and maybe also disable merge and hash joins)
to see if you can force choice of that plan?

regards, tom lane

Re: pg_restore - generated column - not populating

[Tom Lane]

Santosh Udupi  writes:
> Right. pg_dump under the pgAdmin runtime folder works perfectly.  pg_dump
> in postgres13 (ubuntu) does not work. Exact same syntax.

So, are these identical pg_dump versions?  We did fix some things
in this area in 13.2.

regards, tom lane

Re: Custom base type and suboptimal query plans

[Tom Lane]

Ayo  writes:
> No luck. It uses the index now, but seemingly only to loop over it.  The
> integer version uses a HashAggregate, I must have missed something in my
> implementation to make the planner avoid that node. Does it have any special
> type requirements, other than the hash operator class?

Hmm ... did you remember to set the oprcanhash property on the equality
operator?

regards, tom lane

Custom base type and suboptimal query plans

[Ayo]

Hi,

The short version:

I have a database where I converted an integer primary key column to a
custom base type that pretty much amounts to a wrapper around an
integer, and now some queries are resulting in much slower query plans.
Does Postgres have special optimizations for integers that are not
available for custom types, or did I perhaps overlook something?

The longer version:

I have implemented a custom base type that encodes a "type" (which has a
fixed number of values) and a smaller integer into a single 4-byte
value. I've been using this type with success in some parts of the
database - it's much faster than a record type and more convenient than
using multiple columns - so I'm trying to extend its use to more tables.
The full implementation is available online:

SQL: 
https://g.blicky.net/vndb.git/tree/sql/vndbid.sql?id=30070e326f18789f8b82252090b269166d5ade22
C: 
https://g.blicky.net/vndb.git/tree/sql/c/vndbfuncs.c?id=30070e326f18789f8b82252090b269166d5ade22

But now I'm running into cases where queries that used to perform really
well suddenly end up getting a much worse query plan. As an example,
observe the following query, run after doing a VACUUM FULL ANALYZE.


The fast version with integer columns:

=> EXPLAIN (ANALYZE,BUFFERS) SELECT count(*) FROM chars c WHERE c.id IN(SELECT 
cid FROM traits_chars WHERE tid IN(1957, 75));
   QUERY PLAN
-
 Aggregate  (cost=5635.45..5635.46 rows=1 width=8) (actual time=1.273..1.274 
rows=1 loops=1)
   Buffers: shared hit=904
   ->  Nested Loop  (cost=4145.94..5631.93 rows=1410 width=0) (actual 
time=0.525..1.249 rows=301 loops=1)
 Buffers: shared hit=904
 ->  HashAggregate  (cost=4145.65..4159.59 rows=1394 width=4) (actual 
time=0.515..0.579 rows=301 loops=1)
   Group Key: traits_chars.cid
   Batches: 1  Memory Usage: 81kB
   Buffers: shared hit=301
   ->  Bitmap Heap Scan on traits_chars  (cost=19.79..4142.12 
rows=1410 width=4) (actual time=0.078..0.426 rows=301 loops=1)
 Recheck Cond: (tid = ANY ('{1957,75}'::integer[]))
 Heap Blocks: exact=295
 Buffers: shared hit=301
 ->  Bitmap Index Scan on traits_chars_tid  
(cost=0.00..19.43 rows=1410 width=0) (actual time=0.039..0.039 rows=301 loops=1)
   Index Cond: (tid = ANY ('{1957,75}'::integer[]))
   Buffers: shared hit=6
 ->  Index Only Scan using chars_pkey1 on chars c  (cost=0.29..1.07 
rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=301)
   Index Cond: (id = traits_chars.cid)
   Heap Fetches: 0
   Buffers: shared hit=603
 Planning:
   Buffers: shared hit=190
 Planning Time: 0.650 ms
 Execution Time: 1.372 ms
(23 rows)

Same query, but now the chars.id and traits_chars.cid are of the custom 
'vndbid' type:

   QUERY PLAN
-
 Aggregate  (cost=2019373.51..2019373.52 rows=1 width=8) (actual 
time=2273.986..2273.987 rows=1 loops=1)
   Buffers: shared hit=2917
   ->  Nested Loop Semi Join  (cost=19.71..2019370.01 rows=1400 width=0) 
(actual time=0.227..2273.965 rows=301 loops=1)
 Join Filter: (c.id = traits_chars.cid)
 Rows Removed by Join Filter: 28788543
 Buffers: shared hit=2917
 ->  Seq Scan on chars c  (cost=0.00..3573.94 rows=95794 width=4) 
(actual time=0.007..7.717 rows=95794 loops=1)
   Buffers: shared hit=2616
 ->  Materialize  (cost=19.71..4125.57 rows=1400 width=4) (actual 
time=0.000..0.010 rows=301 loops=95794)
   Buffers: shared hit=301
   ->  Bitmap Heap Scan on traits_chars  (cost=19.71..4118.57 
rows=1400 width=4) (actual time=0.080..0.438 rows=301 loops=1)
 Recheck Cond: (tid = ANY ('{1957,75}'::integer[]))
 Heap Blocks: exact=295
 Buffers: shared hit=301
 ->  Bitmap Index Scan on traits_chars_tid  
(cost=0.00..19.36 rows=1400 width=0) (actual time=0.042..0.042 rows=301 loops=1)
   Index Cond: (tid = ANY ('{1957,75}'::integer[]))
   Buffers: shared hit=6
 Planning:
   Buffers: shared hit=178
 Planning Time: 0.565 ms
 Execution Time: 2274.181 ms
(21 rows)

The row estimates for the traits_chars subquery are nearly identical in
both plans and the row estimates for the chars table in the second plan
is accurate, which leads me to suspect that this is not a statistics
issue. I suspected that my custom type may be missing some operators or
functio

Re: pg_restore - generated column - not populating

[Santosh Udupi]

Both are different versions

The following works:


Version: pg_dump (pgAdmin Windows)  version:13.1

Method: Backup using Windows connecting to the remote host:


Command: pg_dump -Fc -p 5432 -h  -d mydb > mydb.backup1 -U
postgres


Restore locally on Ubuntu : pg_restore -d mydb-restore mydb.backup1


---
The following does not work: (Does not populate the generated column
values)

Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)  version 13.2

Method: Backup locally on Ubuntu 20.04

Command: pg_dump -Fc -p 5432 -h localhost -d mydb > mydb.backup2 -U postgres


Restore locally on Ubuntu : pg_restore -d mydb-restore mydb.backup2









On Tue, Feb 23, 2021 at 9:34 AM Tom Lane  wrote:

> Santosh Udupi  writes:
> > Right. pg_dump under the pgAdmin runtime folder works perfectly.  pg_dump
> > in postgres13 (ubuntu) does not work. Exact same syntax.
>
> So, are these identical pg_dump versions?  We did fix some things
> in this area in 13.2.
>
> regards, tom lane
>

Re: Custom base type and suboptimal query plans

[Tom Lane]

Ayo  writes:
> Aaah! That was it, I had totally missed the HASHES and MERGES options to
> CREATE OPERATOR. It works perfectly now that I set those.

Cool.

> Do those options make sense for other operators besides equality, too?

No, they just flag that the operator is equality in some hash or btree
(respectively) opclass.

regards, tom lane

Re: pg_restore - generated column - not populating

[Tom Lane]

Santosh Udupi  writes:
> Both are different versions
> The following works:
> Version: pg_dump (pgAdmin Windows)  version:13.1

> The following does not work: (Does not populate the generated column
> values)
> Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)  version 13.2

Hmm ... well, that would be a regression, but you're going to have
to show us how to reproduce it.  I'm thinking there must be something
odd about the way the table is declared.

regards, tom lane

Re: Custom base type and suboptimal query plans

[Ayo]

(forgot to cc the list, sorry for the duplicate, Tom!)

On 2021-02-23, Tom Lane wrote:
> Ayo  writes:
> > I have a database where I converted an integer primary key column to a
> > custom base type that pretty much amounts to a wrapper around an
> > integer, and now some queries are resulting in much slower query plans.
> > Does Postgres have special optimizations for integers that are not
> > available for custom types, or did I perhaps overlook something?
> 
> The slow query isn't using the chars_pkey1 index, which makes one
> wonder if you have a corresponding index in the custom-type case,

The index exists and is usable in the custom-type case:

=> explain (analyze,buffers) SELECT count(*) FROM chars WHERE id BETWEEN 
'c1000' AND 'c2000';
 QUERY PLAN 
   

 Aggregate  (cost=33.87..33.88 rows=1 width=8) (actual time=0.252..0.253 rows=1 
loops=1)
   Buffers: shared hit=9
   ->  Index Only Scan using chars_pkey1 on chars  (cost=0.29..31.47 rows=959 
width=0) (actual time=0.042..0.179 rows=1001 loops=1)
 Index Cond: ((id >= 'c1000'::vndbid) AND (id <= 'c2000'::vndbid))
 Heap Fetches: 0
 Buffers: shared hit=9
 Planning:
   Buffers: shared hit=97
 Planning Time: 0.383 ms
 Execution Time: 0.308 ms
(10 rows)


> or if you fat-fingered something about the index operator class
> for the custom type.  As of v13 I don't think there's anything in
> that area that custom types can't replicate ... but there certainly
> is plenty of infrastructure for the standard types that you'll need
> to build out if you want equivalent functionality.

Good to know that this ought to be possible, at least. Is there
documentation about what infrastructure exists and how it interacts with
the planner? I've built upon
https://www.postgresql.org/docs/13/xindex.html and implemented
everything that seemed relevant for the type. No doubt I've missed
something, but I can't really tell what that may be.

Re: pg_restore - generated column - not populating

[Santosh Udupi]

Here is my table structure. I will try to get the pg_dump output for this
table in both the versions.

create table tbl_main(

item_id int GENERATED ALWAYS AS IDENTITY,
-
operating_offices int [] GENERATED ALWAYS AS (
nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
-
primary_bill_to_id int   GENERATED ALWAYS as ((info->>'vp')::int) stored ,
--
item_status_array text [] GENERATED ALWAYS as ( array[
coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
-
info jsonb
--
,is_complete bool  GENERATED ALWAYS as (coalesce( (info->>'lf')::bool =
true or (info->>'lg')::bool = true, false)) stored

,is_deleted bool GENERATED ALWAYS as ( coalesce( (info->>'cv')::bool,
false) ) stored
--
,is_a_template bool GENERATED ALWAYS as ( coalesce( (info->>'cw')::bool,
false) ) stored
---
,created_by_user_id int
,created_on timestamptz default now()
--
,primary key(item_id,created_on )


) partition by range (created_on) ;

---=
-- *** index

CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);


---=
 --  partitions

-- default partition
create table tbl_main_partition_default
partition of tbl_main default;

create table tbl_main_partition_2021
partition of tbl_main
for values from ('2020-01-01') to ('2022-01-01');

create table tbl_main_partition_2022
partition of tbl_main
for values from ('2022-01-01') to ('2023-01-01');

create table tbl_main_partition_2023
partition of tbl_main
for values from ('2023-01-01') to ('2024-01-01');

---=



On Tue, Feb 23, 2021 at 10:40 AM Tom Lane  wrote:

> Santosh Udupi  writes:
> > Both are different versions
> > The following works:
> > Version: pg_dump (pgAdmin Windows)  version:13.1
>
> > The following does not work: (Does not populate the generated column
> > values)
> > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)  version 13.2
>
> Hmm ... well, that would be a regression, but you're going to have
> to show us how to reproduce it.  I'm thinking there must be something
> odd about the way the table is declared.
>
> regards, tom lane
>

Re: pg_restore - generated column - not populating

[Adrian Klaver]

On 2/23/21 12:15 PM, Santosh Udupi wrote:
Here is my table structure. I will try to get the pg_dump output for 
this table in both the versions.


create table tbl_main(

item_id int GENERATED ALWAYS AS IDENTITY,
-
operating_offices int [] GENERATED ALWAYS AS (
nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
-
primary_bill_to_id int   GENERATED ALWAYS as ((info->>'vp')::int)
stored ,
--
item_status_array text [] GENERATED ALWAYS as ( array[
coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
-
info jsonb
--
,is_complete bool  GENERATED ALWAYS as (coalesce(
(info->>'lf')::bool = true or (info->>'lg')::bool = true, false))
stored

,is_deleted bool GENERATED ALWAYS as ( coalesce(
(info->>'cv')::bool, false) ) stored
--
,is_a_template bool GENERATED ALWAYS as ( coalesce(
(info->>'cw')::bool, false) ) stored
---
,created_by_user_id int
,created_on timestamptz default now()
--
,primary key(item_id,created_on )


) partition by range (created_on) ;


Which generates(pun intended) the question, why? You are deconstructing 
info into its component parts after the fact, why not just input the 
data directly into the fields.




---=
-- *** index

CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);


---=
  --  partitions

-- default partition
create table tbl_main_partition_default
partition of tbl_main default;

create table tbl_main_partition_2021
partition of tbl_main
for values from ('2020-01-01') to ('2022-01-01');

create table tbl_main_partition_2022
partition of tbl_main
for values from ('2022-01-01') to ('2023-01-01');

create table tbl_main_partition_2023
partition of tbl_main
for values from ('2023-01-01') to ('2024-01-01');

---=



On Tue, Feb 23, 2021 at 10:40 AM Tom Lane > wrote:


Santosh Udupi mailto:em...@hitha.net>> writes:
 > Both are different versions
 > The following works:
 > Version: pg_dump (pgAdmin Windows)  version:13.1

 > The following does not work: (Does not populate the generated column
 > values)
 > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)  version 13.2

Hmm ... well, that would be a regression, but you're going to have
to show us how to reproduce it.  I'm thinking there must be something
odd about the way the table is declared.

                         regards, tom lane




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

Re: pg_restore - generated column - not populating

[Santosh Udupi]

So that it makes it a lot easier for the application logic just to collect
json fields and update in one column "info" instead of including multiple
columns in the insert/update statements.

On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver 
wrote:

> On 2/23/21 12:15 PM, Santosh Udupi wrote:
> > Here is my table structure. I will try to get the pg_dump output for
> > this table in both the versions.
> >
> > create table tbl_main(
> >
> > item_id int GENERATED ALWAYS AS IDENTITY,
> > -
> > operating_offices int [] GENERATED ALWAYS AS (
> > nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
> > -
> > primary_bill_to_id int   GENERATED ALWAYS as ((info->>'vp')::int)
> > stored ,
> > --
> > item_status_array text [] GENERATED ALWAYS as ( array[
> > coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
> > -
> > info jsonb
> > --
> > ,is_complete bool  GENERATED ALWAYS as (coalesce(
> > (info->>'lf')::bool = true or (info->>'lg')::bool = true, false))
> > stored
> > 
> > ,is_deleted bool GENERATED ALWAYS as ( coalesce(
> > (info->>'cv')::bool, false) ) stored
> > --
> > ,is_a_template bool GENERATED ALWAYS as ( coalesce(
> > (info->>'cw')::bool, false) ) stored
> > ---
> > ,created_by_user_id int
> > ,created_on timestamptz default now()
> > --
> > ,primary key(item_id,created_on )
> >
> >
> > ) partition by range (created_on) ;
>
> Which generates(pun intended) the question, why? You are deconstructing
> info into its component parts after the fact, why not just input the
> data directly into the fields.
>
> >
> > ---=
> > -- *** index
> >
> > CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);
> >
> >
> > ---=
> >   --  partitions
> >
> > -- default partition
> > create table tbl_main_partition_default
> > partition of tbl_main default;
> >
> > create table tbl_main_partition_2021
> > partition of tbl_main
> > for values from ('2020-01-01') to ('2022-01-01');
> >
> > create table tbl_main_partition_2022
> > partition of tbl_main
> > for values from ('2022-01-01') to ('2023-01-01');
> >
> > create table tbl_main_partition_2023
> > partition of tbl_main
> > for values from ('2023-01-01') to ('2024-01-01');
> >
> > ---=
> >
> >
> >
> > On Tue, Feb 23, 2021 at 10:40 AM Tom Lane  > > wrote:
> >
> > Santosh Udupi mailto:em...@hitha.net>> writes:
> >  > Both are different versions
> >  > The following works:
> >  > Version: pg_dump (pgAdmin Windows)  version:13.1
> >
> >  > The following does not work: (Does not populate the generated
> column
> >  > values)
> >  > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)  version 13.2
> >
> > Hmm ... well, that would be a regression, but you're going to have
> > to show us how to reproduce it.  I'm thinking there must be something
> > odd about the way the table is declared.
> >
> >  regards, tom lane
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Custom base type and suboptimal query plans

[Ayo]

On 2021-02-23, Tom Lane wrote:
> Have you tried "enable_seqscan = off" (and maybe also disable merge
> and hash joins) to see if you can force choice of that plan?

No luck. It uses the index now, but seemingly only to loop over it.  The
integer version uses a HashAggregate, I must have missed something in my
implementation to make the planner avoid that node. Does it have any special
type requirements, other than the hash operator class?

set enable_seqscan = off;
set enable_hashjoin = off;
set enable_mergejoin = off;
EXPLAIN (ANALYZE,BUFFERS) SELECT count(*) FROM chars c WHERE c.id IN(SELECT cid 
FROM traits_chars WHERE tid IN(1957, 75));
   QUERY PLAN
-
 Aggregate  (cost=2036624.55..2036624.56 rows=1 width=8) (actual 
time=2275.163..2275.164 rows=1 loops=1)
   Buffers: shared hit=567
   ->  Nested Loop Semi Join  (cost=20.10..2036621.02 rows=1412 width=0) 
(actual time=1.402..2275.143 rows=301 loops=1)
 Join Filter: (c.id = traits_chars.cid)
 Rows Removed by Join Filter: 28803593
 Buffers: shared hit=567
 ->  Index Only Scan using chars_pkey1 on chars c  (cost=0.29..2493.95 
rows=95844 width=4) (actual time=0.016..5.955 rows=95844 loops=1)
   Heap Fetches: 0
   Buffers: shared hit=264
 ->  Materialize  (cost=19.80..4154.68 rows=1412 width=4) (actual 
time=0.000..0.010 rows=301 loops=95844)
   Buffers: shared hit=303
   ->  Bitmap Heap Scan on traits_chars  (cost=19.80..4147.62 
rows=1412 width=4) (actual time=0.085..0.636 rows=301 loops=1)
 Recheck Cond: (tid = ANY ('{1957,75}'::integer[]))
 Heap Blocks: exact=297
 Buffers: shared hit=303
 ->  Bitmap Index Scan on traits_chars_tid  
(cost=0.00..19.45 rows=1412 width=0) (actual time=0.046..0.046 rows=301 loops=1)
   Index Cond: (tid = ANY ('{1957,75}'::integer[]))
   Buffers: shared hit=6
 Planning:
   Buffers: shared hit=179
 Planning Time: 0.578 ms
 Execution Time: 2275.328 ms
(22 rows)

Re: Custom base type and suboptimal query plans

[Ayo]

On 2021-02-23, Tom Lane wrote:
> Hmm ... did you remember to set the oprcanhash property on the equality
> operator?

Aaah! That was it, I had totally missed the HASHES and MERGES options to
CREATE OPERATOR. It works perfectly now that I set those.

Do those options make sense for other operators besides equality, too?

Many thanks for the help.

Re: pg_restore - generated column - not populating

[Adrian Klaver]

On 2/23/21 12:57 PM, Santosh Udupi wrote:
So that it makes it a lot easier for the application logic just to 
collect json fields and update in one column "info" instead of including 
multiple columns in the insert/update statements.


I doubt it, but then again this why I don't answer Postgres/JSON SO 
questions anymore. Trying to apply logic to the contortions people go to 
make their life more difficult left me with headaches. At any rate this 
is getting off-topic for the the dump/restore issue you have. When you 
look at the restored tables in each variation of the restore database do 
they look the same as below? If you drop the table in the problem 
database and then recreate it using the script below and then populate 
it with data does it work?





On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 2/23/21 12:15 PM, Santosh Udupi wrote:
 > Here is my table structure. I will try to get the pg_dump output for
 > this table in both the versions.
 >
 > create table tbl_main(
 >
 >     item_id int GENERATED ALWAYS AS IDENTITY,
 >     -
 >     operating_offices int [] GENERATED ALWAYS AS (
 >     nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
 >     -
 >     primary_bill_to_id int   GENERATED ALWAYS as ((info->>'vp')::int)
 >     stored ,
 >     --
 >     item_status_array text [] GENERATED ALWAYS as ( array[
 >     coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
 >     -
 >     info jsonb
 >     --
 >     ,is_complete bool  GENERATED ALWAYS as (coalesce(
 >     (info->>'lf')::bool = true or (info->>'lg')::bool = true, false))
 >     stored
 >     
 >     ,is_deleted bool GENERATED ALWAYS as ( coalesce(
 >     (info->>'cv')::bool, false) ) stored
 >     --
 >     ,is_a_template bool GENERATED ALWAYS as ( coalesce(
 >     (info->>'cw')::bool, false) ) stored
 >     ---
 >     ,created_by_user_id int
 >     ,created_on timestamptz default now()
 >     --
 >     ,primary key(item_id,created_on )
 >
 >
 > ) partition by range (created_on) ;

Which generates(pun intended) the question, why? You are deconstructing
info into its component parts after the fact, why not just input the
data directly into the fields.

 >
 > ---=
 > -- *** index
 >
 >     CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);
 >
 >
 > ---=
 >   --  partitions
 >
 > -- default partition
 > create table tbl_main_partition_default
 > partition of tbl_main default;
 >
 > create table tbl_main_partition_2021
 > partition of tbl_main
 > for values from ('2020-01-01') to ('2022-01-01');
 >
 > create table tbl_main_partition_2022
 > partition of tbl_main
 > for values from ('2022-01-01') to ('2023-01-01');
 >
 > create table tbl_main_partition_2023
 > partition of tbl_main
 > for values from ('2023-01-01') to ('2024-01-01');
 >
 > ---=
 >
 >
 >
 > On Tue, Feb 23, 2021 at 10:40 AM Tom Lane mailto:t...@sss.pgh.pa.us>
 > >> wrote:
 >
 >     Santosh Udupi mailto:em...@hitha.net>
>> writes:
 >      > Both are different versions
 >      > The following works:
 >      > Version: pg_dump (pgAdmin Windows)  version:13.1
 >
 >      > The following does not work: (Does not populate the
generated column
 >      > values)
 >      > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04) 
version 13.2

 >
 >     Hmm ... well, that would be a regression, but you're going to
have
 >     to show us how to reproduce it.  I'm thinking there must be
something
 >     odd about the way the table is declared.
 >
 >                              regards, tom lane
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: pg_restore - generated column - not populating

[Santosh Udupi]

Yes,  this is what we have been doing now:- Backup using pg_dump, create
the new database at the destination, manually create the tables which give
problems, and then do the pg_restore. Another solution for us is to backup
using pg_dump that comes with pgAdmin (Windows), rsync it to the
destination server and then do the pg_restore on the server.

On Tue, Feb 23, 2021 at 3:21 PM Adrian Klaver 
wrote:

> On 2/23/21 12:57 PM, Santosh Udupi wrote:
> > So that it makes it a lot easier for the application logic just to
> > collect json fields and update in one column "info" instead of including
> > multiple columns in the insert/update statements.
>
> I doubt it, but then again this why I don't answer Postgres/JSON SO
> questions anymore. Trying to apply logic to the contortions people go to
> make their life more difficult left me with headaches. At any rate this
> is getting off-topic for the the dump/restore issue you have. When you
> look at the restored tables in each variation of the restore database do
> they look the same as below? If you drop the table in the problem
> database and then recreate it using the script below and then populate
> it with data does it work?
>
>
> >
> > On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 2/23/21 12:15 PM, Santosh Udupi wrote:
> >  > Here is my table structure. I will try to get the pg_dump output
> for
> >  > this table in both the versions.
> >  >
> >  > create table tbl_main(
> >  >
> >  > item_id int GENERATED ALWAYS AS IDENTITY,
> >  > -
> >  > operating_offices int [] GENERATED ALWAYS AS (
> >  > nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
> >  > -
> >  > primary_bill_to_id int   GENERATED ALWAYS as
> ((info->>'vp')::int)
> >  > stored ,
> >  > --
> >  > item_status_array text [] GENERATED ALWAYS as ( array[
> >  > coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
> >  > -
> >  > info jsonb
> >  > --
> >  > ,is_complete bool  GENERATED ALWAYS as (coalesce(
> >  > (info->>'lf')::bool = true or (info->>'lg')::bool = true,
> false))
> >  > stored
> >  > 
> >  > ,is_deleted bool GENERATED ALWAYS as ( coalesce(
> >  > (info->>'cv')::bool, false) ) stored
> >  > --
> >  > ,is_a_template bool GENERATED ALWAYS as ( coalesce(
> >  > (info->>'cw')::bool, false) ) stored
> >  > ---
> >  > ,created_by_user_id int
> >  > ,created_on timestamptz default now()
> >  > --
> >  > ,primary key(item_id,created_on )
> >  >
> >  >
> >  > ) partition by range (created_on) ;
> >
> > Which generates(pun intended) the question, why? You are
> deconstructing
> > info into its component parts after the fact, why not just input the
> > data directly into the fields.
> >
> >  >
> >  >
> ---=
> >  > -- *** index
> >  >
> >  > CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);
> >  >
> >  >
> >  >
> ---=
> >  >   --  partitions
> >  >
> >  > -- default partition
> >  > create table tbl_main_partition_default
> >  > partition of tbl_main default;
> >  >
> >  > create table tbl_main_partition_2021
> >  > partition of tbl_main
> >  > for values from ('2020-01-01') to ('2022-01-01');
> >  >
> >  > create table tbl_main_partition_2022
> >  > partition of tbl_main
> >  > for values from ('2022-01-01') to ('2023-01-01');
> >  >
> >  > create table tbl_main_partition_2023
> >  > partition of tbl_main
> >  > for values from ('2023-01-01') to ('2024-01-01');
> >  >
> >  >
> ---=
> >  >
> >  >
> >  >
> >  > On Tue, Feb 23, 2021 at 10:40 AM Tom Lane  > 
> >  > >> wrote:
> >  >
> >  > Santosh Udupi mailto:em...@hitha.net>
> > >> writes:
> >  >  > Both are different versions
> >  >  > The following works:
> >  >  > Version: pg_dump (pgAdmin Windows)  version:13.1
> >  >
> >  >  > The following does not work: (Does not populate the
> > generated column
> >  >  > values)
> >  >  > Ve

Re: pg_restore - generated column - not populating

[Adrian Klaver]

On 2/23/21 4:25 PM, Santosh Udupi wrote:
Yes,  this is what we have been doing now:- Backup using pg_dump, create 
the new database at the destination, manually create the tables which 
give problems, and then do the pg_restore. Another solution for us is to 


Well that introduces another error, where the restore trips on the 
existing table(s). By the way this is the first time you mentioned 
multiple tables. Do they share similar structure? In any case you should 
not have to do this and in the spirit of identifying the problem and 
fixing it, what happens if you do:


1) Dump locally

2) Restore locally, without creating the tables ahead.

3) Examine and report back here the table(s) schema(and sample data) 
after the fresh restore.


4) Drop the table(s) and create from script and populate. Report on 
whether they work.


backup using pg_dump that comes with pgAdmin (Windows), rsync it to the 
destination server and then do the pg_restore on the server.





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

Re: pg_restore - generated column - not populating

[Santosh Udupi]

Sure. I will try that.

On Tue, Feb 23, 2021 at 4:42 PM Adrian Klaver 
wrote:

> On 2/23/21 4:25 PM, Santosh Udupi wrote:
> > Yes,  this is what we have been doing now:- Backup using pg_dump, create
> > the new database at the destination, manually create the tables which
> > give problems, and then do the pg_restore. Another solution for us is to
>
> Well that introduces another error, where the restore trips on the
> existing table(s). By the way this is the first time you mentioned
> multiple tables. Do they share similar structure? In any case you should
> not have to do this and in the spirit of identifying the problem and
> fixing it, what happens if you do:
>
> 1) Dump locally
>
> 2) Restore locally, without creating the tables ahead.
>
> 3) Examine and report back here the table(s) schema(and sample data)
> after the fresh restore.
>
> 4) Drop the table(s) and create from script and populate. Report on
> whether they work.
>
> > backup using pg_dump that comes with pgAdmin (Windows), rsync it to the
> > destination server and then do the pg_restore on the server.
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: Permission inconsistency with views that call functions

[David Wheeler]

Thanks for the reply

> VIEWs operate as if they were "SECURITY DEFINER".

My concern is that the view is not acting as a security barrier underneath 
which all access is evaluated using the view owner context; in some 
circumstances (when the view calls a function) the access is evaluated using 
the caller’s context.

Caller
   |
   |
  V
- View --   Security definition layer
   |
  | (b)
   ||
(a)|v
   |  Function (not SECURITY DEFINER or explicitly SECURITY INVOKER)
   ||
   ||
   ||
  Vx
   Table


I would expect that everything underneath the View would use the view owner to 
evaluate permissions. However it seems that in scenario (b) it enters a new 
security context from the caller, rather than inheriting it from the view.

> The solution to your issue is possibly to make the function "SECURITY 
> DEFINER".

This works, but I see this as a workaround, because the function is simply a 
utility that makes understanding the data in the table a little easier. Why 
should it be security definer? If you don’t have access to the table you 
shouldn’t be able to use the function to access it.


Regards,

David

On 24/2/21, 2:41 am, "Joe Conway"  wrote:
On 2/22/21 10:32 PM, David Wheeler wrote:
> I’m seeing some inconsistency with how permissions are enforced within views. 
> In
> particular, if the view accesses a table directly, then the table is 
> accessible,
> however if the view uses a function to access the table then permission is 
> denied.

Without looking too closely at the details, I can almost guarantee that the
issue is that FUNCTIONs default to "SECURITY INVOKER" whereas VIEWs operate as
if they were "SECURITY DEFINER". See slide 33 here:

http://joeconway.com/presentations/security-pgcon2020.pdf

The solution to your issue is possibly to make the function "SECURITY DEFINER".

I have mused previously (not sure if I ever did on the lists, but in any case)
that it would be cool if VIEWs could have the option to be either DEFINER or
INVOKER so that VIEWs and FUNCTIONs could be treated the same, but no efforts
have been made in that direction as far as I am aware.

HTH,

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

getting tables list of other schema too

[Atul Kumar]

Hi,

I have postgres 9.6 cluster running on centos 7 machine.

when I set search_path to any user made schema with below command

[enterprisedb@stg-edb02 ~ 01:51:39]$ psql edb
edb=# \c test

set search_path to college;

and after listing the tables with command \dt, we should get list of
tables of schema college only.

but here I am getting list of tables of schema college and list of
tables of schema sys along with it.


Why is it happening, please suggest.


test=# \dt
List of relations
 Schema |  Name   | Type  |Owner
+-+---+--
 college | ta_rule_error   | table | college
 college | team_import | table | college
 college | test_24022021   | table | enterprisedb
 sys| callback_queue_table| table | enterprisedb
 sys| dual| table | enterprisedb
 sys| edb$session_wait_history| table | enterprisedb
 sys| edb$session_waits   | table | enterprisedb
 sys| edb$snap| table | enterprisedb
 sys| edb$stat_all_indexes| table | enterprisedb
 sys| edb$stat_all_tables | table | enterprisedb
 sys| edb$stat_database   | table | enterprisedb
 sys| edb$statio_all_indexes  | table | enterprisedb
 sys| edb$statio_all_tables   | table | enterprisedb
 sys| edb$system_waits| table | enterprisedb
 sys| plsql_profiler_rawdata  | table | enterprisedb
 sys| plsql_profiler_runs | table | enterprisedb
 sys| plsql_profiler_units| table | enterprisedb
 sys| product_component_version   | table | enterprisedb
 sys| scheduler_0100_component_name   | table | college
 sys| scheduler_0200_program  | table | college
 sys| scheduler_0250_program_argument | table | college
 sys| scheduler_0300_schedule | table | college
 sys| scheduler_0400_job  | table | college
 sys| scheduler_0450_job_argument | table | college