Disabling triggers on tables dumped with pg_extension_config_dump()

[Michel Pelletier]

Hello,

I have an extension that contains a configuration table with a before
insert trigger that I setup to dump with pg_extension_config_dump().
Because the table and trigger are setup during CREATE EXTENSION time
emitted by the dump, and the dumped table is then COPY'd outside of that,
the triggers get re-fired.  This doesn't happen for non-extension tables,
which don't run CREATE TRIGGER until after the COPY.

Trying to find the best way to solve this problem in general for extension
users so that they don't have to use --disable-triggers on the dump or
restore as this seems like a rare edge case.  Can anyone suggest a better
way?

I'm wondering, would it make sense to extend pg_extension_config_dump()
with a disable_triggers argument, that defaults to false, and if set to
true, pg_dump will emit DISABLE/ENABLE guards around the config table's
COPY statement?  Just a wild idea.

Thanks!

-Michel

Re: pg_dumpall renders ALTER TABLE for a view?

[Michel Pelletier]

On Thu, Dec 15, 2022 at 9:15 AM Tom Lane  wrote:

> Michel Pelletier  writes:
> > I get that this is synonymous in most cases, except when there is an
> event
> > trigger for `ALTER TABLE`, it ends up firing the event trigger for the
> > views, and any event triggers expected to fire on `ALTER VIEW` statements
> > do not fire.
>
> > Is this something we can contribute a fix for, or is this some kind of
> > necessary b/w compat issue that must remain?
>
> We're not likely to change the fact that you're allowed to write ALTER
> TABLE for this, so if your event triggers get broken by doing that
> you'd best fix the event triggers.
>

For sure, and we're working on fixing the triggers, I should have been more
clear that I'm not suggesting any changes to the server behavior, just to
the dump/restore process.


> I don't have any great objection to making pg_dump emit the more modern
> spelling (I think ... you'd need to look into pg_restore to make sure
> it's not assuming something in this area).  But doing that won't
> really remove the hazard.
>

Agree it won't remove the hazard, but should reduce the kind of collateral
damage we encountered.  Will examine pg_restore as you suggested to scope
the work better.

Thank you!

-Michel

pg_dumpall renders ALTER TABLE for a view?

[Michel Pelletier]

Hello,

We found the root cause for an issue we encountered restoring a saved
database stored with pg_dumpall, but during this investigation we realized
that pg_dump/all renders `ALTER TABLE` statements for views, for example to
convey ownership.

I get that this is synonymous in most cases, except when there is an event
trigger for `ALTER TABLE`, it ends up firing the event trigger for the
views, and any event triggers expected to fire on `ALTER VIEW` statements
do not fire.

Is this something we can contribute a fix for, or is this some kind of
necessary b/w compat issue that must remain?  Looking at the history of
ALTER VIEW it looks like it has existed since at least 9.0, so it seems
safe to me from a b/w compat standpoint to render the correct statement.

Thanks!

-Michel

Re: Dumping security labels for extension owned tables?

[Michel Pelletier]

On Wed, Dec 14, 2022 at 11:29 AM Julien Rouhaud  wrote:

>
> Note that if a table is part of an extension, pg_extension_config_dump
> will only lead pg_dump to emit the table data, not the table DDL.  The
> table itself must be entirely created by the extension script, and any
> modification done afterwards to a table (or any other object) that is
> part of an extension (a security label but also a comment, new columns
> or anything else) will just be lost.  That's how extensions are
> designed, and indeed trying to do inter-extension dependencies like
> this isn't going to work.
>

Ah my bad, sorry for the noise Julien, the create extension statement is
correctly dumped and it applies the label, so I shouldn't have been
expecting the label in the dump (as you informed me).  There is some other
root cause for the problem we are observing and we are continuing to dig
into it.

Thank you for your quick reply!

-Michel

Dumping security labels for extension owned tables?

[Michel Pelletier]

Hello,

I have an issue I've run into that is puzzling me, I have an extension
pgsodium that uses SECURITY LABEL to trigger the creation of encrypting
triggers and a decrypting view.  When a table not associated with an
extension is dumped, the label gets dumped as well, and that's fine.

But if I have an extension that creates a table (and is "owned" by it) and
I apply a label, the table gets dumped (when I use
pg_extension_config_dump), but the label does not get dumped.  If I run
`ALTER EXTENSION  DROP TABEL ` the label does
get correctly dumped.

Is there a reason why extension associated tables do not have their label's
dumped, or is this a bug in pg_dump where it's dumping the table but not
the label?  Hoping someone might have a suggestion for me before I go
digging into it too much further.

Thanks!

-Michel

Re: Is there a way to detect that code is inside CREATE EXTENSION?

[Michel Pelletier]

>
> At the C-code level you can check the creating_extension global variable,
> or maybe better look at the in_extension fields of CollectedCommands.
>

Thanks Tom!

That was the hint I needed, looks like pg_event_trigger_ddl_commands() has
an in_extension boolean that seems like it will do what I need?   If I
understand you correctly that's what you're referring to with
CollectedCommands.   Testing it now but that looks like the answer for me,
if any of those are true then I can just skip the regeneration.

-Michel

Is there a way to detect that code is inside CREATE EXTENSION?

[Michel Pelletier]

I'm working with an event trigger that fires on ALTER TABLE and regenerates
certain objects, but unfortunately those objects end up being owned by any
extensions that run ALTER TABLE and any subsequent alterations fail to
regenerate because they are owned by that extension.

Ideally, I'd like to be able to detect inside my trigger if I'm being
called from CREATE EXTENSION or not, but I can't find any obvious way to
detect that.  I hope this isn't obvious and I just missed something in the
documentation.  Does anyone have any pointers or hacks they know of that
can accomplish this?

Thanks,

-Michel

An self-contained example "expanded" C data type

[Michel Pelletier]

The docs for expanded data types are good, but for a working example you
have to go trolling through the array data type source code, which is
enlightening but a pretty heavy lift for me especially if I have to come
back to it after some time.

So I decided to distill what I could glean from the source into a simple,
self-contained, completely useless extension called pgexpanded:

https://github.com/michelp/pgexpanded

I'd like to ask 1) does this seem like a useful project to other people?
If there is interest in me taking it further I'll expand it with some more
documentation and pgtap tests.  and if you think "yes" to 1, then 2) would
those who know a lot more on this subject than I do would like to review
the code? I'll put on my finest asbestos.

Thanks,

-Michel

Re: CASCADE/fkey order

[Michel Pelletier]

You can benchmark your scenario with and without constraint using a tool
like nancy:

https://gitlab.com/postgres-ai/nancy

it lets you A/B test different configurations with your own scenarios or
using pgbench synthetic workloads.

-Michel

On Wed, Jul 22, 2020 at 9:27 AM Samuel Nelson 
wrote:

> I checked, and changing the `bazinga_foo_bar` constraint to:
>
> alter table bazinga add constraint bazinga_foo_bar foreign key (foo_id,
> bar_id) references foo_bar (foo_id, bar_id) deferrable initially deferred;
>
> seems to fix it to work as we were expecting.  Is that particularly
> costly?  Should I only set the constraint to be deferred when we really
> need it?  Would it be more efficient to perform the deletes explicitly
> within a transaction rather than relying on the cascades and deferring that
> one constraint?
>
> Our resident ex-Oracle DBA said that deferred constraints used to be
> heavily recommended against, but he also admitted that he hasn't kept up
> with that in the past 10 years.
>
> -Sam
>
> https://github.com/nelsam
>
> "As an adolescent I aspired to lasting fame, I craved factual certainty,
> and
> I thirsted for a meaningful vision of human life -- so I became a
> scientist.
> This is like becoming an archbishop so you can meet girls."
> -- Matt Cartmill
>
>
> On Wed, Jul 22, 2020 at 10:31 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Wed, Jul 22, 2020 at 8:24 AM Samuel Nelson 
>> wrote:
>>
>>> Is there a way to force the delete to cascade to tables in a specific
>>> order?
>>>
>>
>> No really, but you can defer constraint checking.
>>
>> https://www.postgresql.org/docs/12/sql-set-constraints.html
>>
>> David J.
>>
>>
>

Re: Multitenent architecture

[Michel Pelletier]

On Tue, Jul 21, 2020 at 7:47 AM Vasu Madhineni 
wrote:

> Hi All,
>
> Our project uses each database for tenant, But how can we restrict
> tenant resources?
> Ex: Tenent1 has to use 20% resource and Tenent2 has to use 10% resource,
> how can we restrict users like this.
>

See https://wiki.postgresql.org/wiki/Priorities

-Michel

Re: DB Authentication with Label Security

[Michel Pelletier]

On Fri, Jul 17, 2020 at 12:08 PM Ailleen Pace 
wrote:

> Oracle has a product called Oracle Label Security using Oracle Internet
> Directory.  Does PostgreSQL have a similar capability?
>
> Thank you in advance!
>

Hi Ailleen,

Googling it appears the same functionality in Postgres is called Row Level
Security (RLS).

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

-Michel

Re: how to "explain" some ddl

[Michel Pelletier]

Marc,

If you add a check constraint that proves the new child partition has no
out of bounds rows, then the ATTACH PARTITION will not block:

"Before running the ATTACH PARTITION command, it is recommended to create a
CHECK constraint on the table to be attached matching the desired partition
constraint. That way, the system will be able to skip the scan to validate
the implicit partition constraint. Without the CHECK constraint, the table
will be scanned to validate the partition constraint while holding an ACCESS
EXCLUSIVE lock on that partition and a SHARE UPDATE EXCLUSIVE lock on the
parent table. It may be desired to drop the redundant CHECK constraint
after ATTACH PARTITION is finished."

https://www.postgresql.org/docs/current/ddl-partitioning.html

As for your high dimension table with lots of indexes, are you sure they
are all being used?  I almost always find my legacy customers have many
indexes that are constantly being updated but are never used by their
applications due to either "framework confusion" or just overzealous
indexing.   Here's a good article by Laurenze Albe on the subject:

https://www.cybertec-postgresql.com/en/get-rid-of-your-unused-indexes/

-Michel

On Tue, Jul 14, 2020 at 12:32 PM Marc Millas  wrote:

> Hi Tom,
> a few tests later.
> Looks like when you add a partition as default, all tupples of it are
> read, even if there is an index on the column that is the partition key.
> this do explain our attach time. We are going to clean the default
> partition...
>
> regards,
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Tue, Jul 14, 2020 at 7:05 PM Tom Lane  wrote:
>
>> Marc Millas  writes:
>> > We would like to understand where an alter table attach partition spend
>> its
>> > time.
>> > to my understanding, explain doesnt do this.
>>
>> Nope :-(.  As our DDL commands have gotten more complicated, there's
>> been some discussion of adding that, but nothing's really been done
>> yet.
>>
>> There is some progress-monitoring support for some DDL commands now,
>> but that's not quite the same thing.
>>
>> > for a BI job we have a partitionned table with 1800+ partitions.
>>
>> TBH I'd recommend scaling that down by at least a factor of ten.
>> We are not at a point where you can expect that all operations will
>> be cheap even with thousands of partitions.  We may never be at that
>> point, although people continue to chip away at the bottlenecks.
>>
>> regards, tom lane
>>
>

Re: Listen/Notify feedback

[Michel Pelletier]

On Sat, Jul 11, 2020 at 10:44 AM Brian Dunavant  wrote:

> One aspect is if there is no one listening when a notify happens, the
> message is lost (e.g. no durability).   If this is important to you, it can
> be addressed by writing the messages to a table as well when you NOTIFY,
> and the listener deletes messages after they are processed.  On connection
> the listener can query the table to catch up on any missed messages, or
> messages that were mid-process during a crash.  This is trickier with more
> than one listener.   This isn't a whole lot more efficient than just using
> the table alone, but it saves you from having to poll so better response
> times.
>

Good advice from Brian here that mirrors my own experience, I'd like to
point out that if you do go the multiple listener route working a
persistent table, it's important to avoid races with SELECT FOR UPDATE SKIP
LOCKED.

https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE

I know this is old news to most of the people on this list, but I've run
into enough folks who don't know about this little gem that I figured I'd
mention it, it's saved my bacon more than once.

-Michel


> On Sat, Jul 11, 2020 at 8:58 AM Rita  wrote:
>
>> I am investigating various pub/sub tools such as ActiveMQ, Rabbit, Redis,
>> etc.I came across Postgresql Listen/Notify and was easily able to write
>> code to listen to messages. For the people who have been using this for a
>> while: what are its downsides, things to consider when writing good code
>> that use pub/sub, how do you deal with large messages, can I have
>> subscribers listen to replica nodes?
>>
>> Thanks
>> --
>> --- Get your facts first, then you can distort them as you please.--
>>
>

Re: invalid non-zero objectSubId for object class

[Michel Pelletier]

On Fri, Jul 10, 2020 at 5:37 AM Alvaro Herrera 
wrote:

> On 2020-Jul-09, Michel Pelletier wrote:
>
> > I restored a snapshot and I can drop the tables there, so we'll likely
> > proceed  to swap the replicas over tomorrow.  I have this corrupted
> > instance i can continue to debug on if necessary.  There seem to be some
> > other issues now that we're investigating, like a max(timestamp) query on
> > the old instance is doing a seqscan on all the partitions instead of
> using
> > an index that's there, the new instance from the snapshot correctly uses
> > the index, so I suspect further corruption.
>
> This being RDS makes it very difficult to debug.  For example I would
> suggest a debugger to find the problem, but you don't have access to
> that.
>

Yes agreed, we'll probably terminate the instance today if there's no
further lines of investigation.  I know this isn't their forum, but I don't
get why AWS pins all their extension versions to the postgres version, If
you use 12.2 on rds, you get pglogical 2.3.0.  If you want 2.3.1, you have
to upgrade to pg 12.3.  I don't get that logic, shouldn't 2.3.1 be the
current version on both?  What's the point of them supporting 12.2 if they
ship an extension version that 2ndQ says you shouldn't use?

Anyway, just musing out loud, don't expect any kind of answer to that here.
:)

Thanks again everyone,

-Michel



> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: invalid non-zero objectSubId for object class

[Michel Pelletier]

I restored a snapshot and I can drop the tables there, so we'll likely
proceed  to swap the replicas over tomorrow.  I have this corrupted
instance i can continue to debug on if necessary.  There seem to be some
other issues now that we're investigating, like a max(timestamp) query on
the old instance is doing a seqscan on all the partitions instead of using
an index that's there, the new instance from the snapshot correctly uses
the index, so I suspect further corruption.

Thanks for your help!

-Michel

Re: invalid non-zero objectSubId for object class

[Michel Pelletier]

On Thu, Jul 9, 2020 at 5:32 PM Tom Lane  wrote:

> Alvaro Herrera  writes:
> > On 2020-Jul-09, Michel Pelletier wrote:
> >> I don't seem to have either:
> >>
> >> dev=> select * from pg_depend where classid = 297108 or refclassid =
> 297108;
> >> classid | objid | objsubid | refclassid | refobjid | refobjsubid |
> deptype
> >>
> -+---+--++--+-+-
> >> (0 rows)
>
> > Maybe try looking in pg_shdepend?  Or pg_description?
>
> I think it has to be pg_depend, because there'd be no reason to
> be calling getObjectClass() on a entry from one of those other
> catalogs during a DROP TABLE.
>
> So my thoughts are now turning towards there being corruption
> in pg_depend's indexes as well as the heap.  Try doing the above
> query after turning enable_indexscan and enable_bitmapscan off.
>

Unfortunately still no refs there:

dev=> set enable_indexscan = off;
SET
dev=> set enable_bitmapscan = off;
SET
dev=> select * from pg_depend \g | grep 297108
dev=> select * from pg_depend where classid = 297108::oid or refclassid =
297108::oid;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
-+---+--++--+-+-
(0 rows)

dev=> explain select * from pg_depend where classid = 297108::oid or
refclassid = 297108::oid;
  QUERY PLAN
---
 Seq Scan on pg_depend  (cost=0.00..336.87 rows=2 width=25)
   Filter: ((classid = '297108'::oid) OR (refclassid = '297108'::oid))
(2 rows)

-Michel


>
> regards, tom lane
>

Re: invalid non-zero objectSubId for object class

[Michel Pelletier]

On Thu, Jul 9, 2020 at 5:26 PM Alvaro Herrera 
wrote:

> On 2020-Jul-09, Michel Pelletier wrote:
>
> > Hi Tom, thanks for getting back so quick:
> >
> > I don't seem to have either:
> >
> > dev=> select * from pg_depend where classid = 297108 or refclassid =
> 297108;
> >  classid | objid | objsubid | refclassid | refobjid | refobjsubid |
> deptype
> >
> -+---+--++--+-+-
> > (0 rows)
>
> Maybe try looking in pg_shdepend?  Or pg_description?
>

Hi Álvaro,

It doesn't occur in either of those either
dev=> select * from pg_description \g | grep 297108
dev=> select * from pg_shdepend \g | grep 297108

-Michel


>
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: invalid non-zero objectSubId for object class

[Michel Pelletier]

On Thu, Jul 9, 2020 at 4:18 PM Tom Lane  wrote:

> Michel Pelletier  writes:
> > On a 12.3 AWS RDS instance, I get the following error when trying to drop
> > either of two tables:
>
> > dev=> drop table current_flight;
> > ERROR:  invalid non-zero objectSubId for object class 297108
> > dev=> drop table flight;
> > ERROR:  invalid non-zero objectSubId for object class 297108
>
> This looks like corrupt data in pg_depend, specifically an entry or
> entries with classid or refclassid = 297108, which should not happen
> (the classid should always be the OID of one of a short list of system
> catalogs).  You could try poking around in pg_depend to see if you
> can identify any obviously-bogus rows.
>

Hi Tom, thanks for getting back so quick:

I don't seem to have either:

dev=> select * from pg_depend where classid = 297108 or refclassid = 297108;
 classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype
-+---+--++--+-+-
(0 rows)

I'm not sure what a bogus row would look like.


> No idea how it got that way.  Have you had any database crashes or the
> like?
>

No crashes, but a restart and one upgrade.  On Sunday and Monday, at
exactly UTC midnight  we run a cron job to create a new partition for an
unrelated table and attach it to a pglogical replication set.   I updated
the procedure on saturday to create two new partitions for two unrelated
tables, and that somehow caused an issue on 12.2 / pglogical 2.3.0 that
caused an error, but not a crash.  What's puzzling is that the two
partition creation still worked, and replicated to all downstream
consumers, but from that point on replication ceased and consumers logged
the error in the link below:

https://github.com/2ndQuadrant/pglogical/issues/267

This spooled up changes on the RDS primary until it filled up the storage.
On sunday we resized the instance and restarted, and reinitialized the
pglogical setup which restarted replication.  On monday the error happened
again at midnight, and we restarted replication and upgraded to 12.3/2.3.1
on tuesday as recommended in the issue.  It has thus run till now without
error and has been replicating nicely so have assumed that issue is fixed.

Neither of these two tables are involved in the midnight job, they're no
longer used and I was hoping to clean them up.  I guess my concern should
be, is there additional possible corruption I can check for?  And if that's
ok is there some manual intervention I can do to drop the tables?

Thanks,

-Michel


>
> regards, tom lane
>

invalid non-zero objectSubId for object class

[Michel Pelletier]

On a 12.3 AWS RDS instance, I get the following error when trying to drop
either of two tables:

dev=> drop table current_flight;
ERROR:  invalid non-zero objectSubId for object class 297108
dev=> drop table flight;
ERROR:  invalid non-zero objectSubId for object class 297108

I can create and drop new tables, but these two I cannot drop.

Both tables were at one point replicated using pglogical, but I've removed
them from their replication sets and nothing depends on them that I can
see.  The replication of other tables is still proceeding normally.

Anyone have any thoughts on this?  The db was also upgraded seemingly
successfully from 12.2 a couple of days ago and otherwise seems to be
working fine.

Thanks for any pointers!

-Michel

Re: Application Level Encryption

[Michel Pelletier]

On Sun, Jul 5, 2020 at 3:23 PM Sam Gendler 
wrote:

>
>
> On Sun, Jul 5, 2020 at 11:41 AM Michel Pelletier <
> pelletier.mic...@gmail.com> wrote:
>
>>
>>
>> I'm working on an approach where the decrypted DEK only lives for the
>> lifetime of a transaction, this means hitting the kms on every transaction
>> that uses keys.  It will be slower, but the time the decrypted key stays in
>> memory would be minimized.
>>
>
> Watch out for KMS api quotas if you go that route.  Their docs don't state
> what the default quotas are, so you have to go to your quotas page in the
> console to find out, but they likely aren't very high and might well be
> exceeded by the transaction rate on even a relatively small db instance.
>

Thanks for pointing that out, it's true that it's a limited route with
cloud KMS.   If you control the device like a Zymkey in a secure enclosure,
the cost is minimal, although the key derivation rate is very slow.

-Michel


>
>
>>

Re: Application Level Encryption

[Michel Pelletier]

On Sun, Jul 5, 2020 at 10:14 AM Zahir Lalani 
wrote:

>
>
>
>
> So what Michael has posted above is actually the target. We are hosted in
> Google Cloud and have been told that we need to use a key manager outside
> of PG (Google have KMS) and that it must have a master key which is rotated
> regularly. We are having a debate about what to encrypt – “it must encrypt
> our data” – we are multi-tenanted and also we have data that is not client
> facing in each tenant. I worry about applying app level to all data for
> sheer performance reasons.
>
>
>
> We have suggested we only encrypt what is truly client data so that we do
> not have to refactor everything.
>
>
>
> The other challenge we have is the external reporting tools we use – none
> of these will work as, and we cannot pass them the unencrypted data.
>
>
>
> So I wanted to understand approaches that could be taken and how to
> minimise performance impacts and how to manage the use of 3rd party tools
>

pgosodium currently supports the following approach: you store your Data
Encryption Key (DEK) on the local filesystem that is encrypted by the Key
Encryption Key (KEK) stored in google's KMS.  When the server boots,
pgsodium calls a script "pgsodium_getkey" that can decrypt the DEK via
googles REST API (you would have to supply this script), this decrypted key
is then stored in server memory, but is not accessible to SQL
non-superusers (it is possible with contortions for a superuser to get the
key depending on how you set it up).  The decrypted DEK is then used to
"derive" keys used by the application by key id.  You only ever store the
key ids, never the keys.  You can now encrypt and decrypt data with the
given key id.  Keys also have an 8 byte "context" so key 1 in context
"tenant01"  is different from key 1 in "tenant02".  You can derive up to
bigint keys per context, so you can use a different key id for every row of
data, if you want to go that far.  There's an example of that in the docs.

Further steps can include deleting the getkey script and the stored
encrypted DEK after server startup.  If the server reboots you must
orchestrate how to place those back for startup to procede.

Note that any extension can always access all server memory, so be careful
what you install.

I'm working on an approach where the decrypted DEK only lives for the
lifetime of a transaction, this means hitting the kms on every transaction
that uses keys.  It will be slower, but the time the decrypted key stays in
memory would be minimized.

-Michel



>
>
> Hope that makes sense
>
>
>
> Z
>
>
>

Re: Application Level Encryption

[Michel Pelletier]

Hi Zahir,

pgsodium is a new-ish encyption extension built around the libsodium
encryption API.

https://github.com/michelp/pgsodium

It supports calling a script to load a hidden key in memory and use that
key to derive other keys.  There's an example shown in the documentation.
I'm working on support for the Zymkey hardware security module, as well as
support for the AWS key management API.

-Michel

On Sat, Jul 4, 2020 at 11:22 PM Zahir Lalani 
wrote:

> Hello
>
>
>
> Does anyone have any serious experience of implementing app level
> encryption with strong key management?
>
>
>
> If so would like to arrange an offline chat please
>
>
>
> Z
>

Re: Hiding a GUC from SQL

[Michel Pelletier]

On Sun, Jun 21, 2020 at 10:21 PM raf  wrote:

> Laurenz Albe wrote:
>
> > > But only mostly useless. :-) There are ways to limit the power of the
> > > superuser. On Linux, for instance, "sysctl kernel.yama.ptrace_scope=3"
> > > prevents tracing, debugging, and reading another process's memory, even
> > > by the superuser, and the only way to turn it off is via a (hopefully
> > > noticeable) reboot.
> >
> > Interesting.  Will this block a user from debugging his own processes?
>
> Yes.
>

Thanks for the tip raf!


> > Perhaps you can plug that hole that way, but that was just the first
> thing
> > that popped in my head.  Don't underestimate the creativity of attackers.
> > I for one would not trust my ability to anticipate all possible attacks,
> > and I think that would be a bad security practice.
>
> Yes, but that's no reason not to perform as much risk
> assessment and mitigation as you can afford/justify.
> Not being able to prevent all attacks is no reason not
> to prevent those that you can. :-) Nobody said anything
> about underestimating anyone or trusting anyone.
>

I'm trying to take as layered an approach as possible, aggressively hiding
the key in postgres memory is one approach I'm taking as the out of the box
experience, but I'm also working on AWS KMS integration and a Zymkey HSM
integration.  In those cases, keys would be fetched on demand, and
unencrypted keys would only live in memory for a short transaction lifetime
while being used, and then discarded, and I think your ptrace_scope trick
will help add a layer in either case.

-Michel

Re: Hiding a GUC from SQL

[Michel Pelletier]

On Thu, Jun 18, 2020 at 7:47 AM Laurenz Albe 
wrote:

> On Wed, 2020-06-17 at 13:23 -0700, Michel Pelletier wrote:
> >
> > Any thoughts on weaknesses to this approach would be welcome.  Thanks!
>

> A superuser can access files and start programs on the server machine.
>

> A dedicated superuser may for example attach to PostgreSQL with a debugger
> and read the value of the variable.
>

Preventing access from regular users is pretty solid I believe, but you're
right superusers is going to be a real challenge.  It is discouraging that
just about every postgres deployment I've inherited over the years has some
web user interacting process logging in as a superuser.  There are
seemingly infinite web frameworks that do this out of the box like it's a
feature.  If the database is coupled to the entire web via a superuser web
client then I consider that a compromised system already and doing
something like crypto, or banking, or PII is insane.

So, that being said I'm assuming some level of reasonable when I want to
avoid access for superusers.  This is mainly to avoid mistakes that a
superuser could make like accidentally leaking a key.For that
definition of "reasonable" I guess there are at least two risks here, one
is accessing process state by invoking inspection tools like debuggers, and
another is running the getkey script like `COPY foo FROM PROGRAM
'/usr/share/postgresql/13/extension/pgsodium_getkey';`

For the first situation I can't think of any mitigation other than
documenting and recommending that things like debuggers not be installed on
systems that do crypto (or banking, or PII, etc).

For the second situation there are a couple mitigations at the expense of
some annoyance lik getkey programs that prompt for the key on boot from an
interactive console. For non-interactive getkeys  I'm considering an
optional mode where the getkey program is deleted by the extension
initialization after one use.  The key fetcher program must be placed in
the right dir on every server start by some external process.


> It is mostly useless to try to keep a superuser from doing anything that
> the "postgres" operating system user can do.
>

Agreed, thanks for your suggestions!

-Michel


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

Re: Hiding a GUC from SQL

[Michel Pelletier]

On Wed, Jun 17, 2020 at 3:55 PM Tom Lane  wrote:

> Michel Pelletier  writes:
> > In my extension pgsodium I'm defining a custom variable at startup to
> store
> > a key:
>
> > https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107
>
> > I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
> GUC_NOT_IN_SAMPLE
> > | GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures
> the
> > value.  This idea was inspired from the pgcryptokey module from Bruce
> > Momjian.
>
> I guess I'm wondering why you're making it a GUC at all, if you don't
> want any of the GUC facilities to apply.
>

An excellent point as it's loaded pre-fork I guess I don't need any of that
stuff.


> It seems like if you want to be this paranoid, you'd be better off
> not exposing the variable to the GUC machinery in the first place.
> You could use a custom set-function (like setseed) to replace the one
> bit of functionality you do want.
>

Thanks!  I've implemented your suggestion similar to how setseed stores its
data.


>
> regards, tom lane
>

Hiding a GUC from SQL

[Michel Pelletier]

In my extension pgsodium I'm defining a custom variable at startup to store
a key:

https://github.com/michelp/pgsodium/blob/master/src/pgsodium.c#L1107

I'm using the flags GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE
| GUC_DISALLOW_IN_FILE, and a custom "no show" show hook that obscures the
value.  This idea was inspired from the pgcryptokey module from Bruce
Momjian.

The value cannot be shown either with SHOW or current_setting() and it does
not appear in pg_settings.  From what I can tell, the value is inaccessible
from SQL, but I think it's worth asking the experts if there is some other
demonstrable way, from SQL, that this value could be leaked even to a
superuser.  no sql level user should be able to see this value, only a C
function, like the pgsodium_derive() from which to derive other keys,
should be able to see it.  I realize that someone with external process
access can get the key, my  goal is to prevent accessing it from SQL.

Any thoughts on weaknesses to this approach would be welcome.  Thanks!

-Michel

Re: Should I enforce ssl/local socket use?

[Michel Pelletier]

On Sat, Jun 6, 2020 at 1:52 PM Tom Lane  wrote:

> Michel Pelletier  writes:
> > I'm the author of the pgsodium cryptography library.  I have a question
>
> > Any thoughts?  Is this an insufferably rude attitude?
>
> I would say yes.
>

I'd say that settles it then, thank you!

-Michel

Should I enforce ssl/local socket use?

[Michel Pelletier]

Hello,

I'm the author of the pgsodium cryptography library.  I have a question
about a best practice I'm thinking of enforcing.  Several functions in
pgsodium generate secrets, I want to check the Proc info to enforce that
those functions can only be called using a local domain socket or an ssl
connection.  If the connection isn't secure by that definition, secret
generating functions will fail.

If someone really wants to point the gun at their foot, they can connect
with an unsecured proxy.  My goal would be to make bypassing the check
annoying.

Any thoughts?  Is this an insufferably rude attitude?  Are there scenarios
where one can foresee needing to generate secrets not over ssl or a domain
socket?

-Michel

Re: Multitenent architecture

[Michel Pelletier]

On Sat, Jun 6, 2020 at 3:14 AM Vasu Madhineni 
wrote:

> Hi Rob,
>
> Our environment is medical clinical data, so each clinic as a tenant.
> Approximately 500+ tenants with 6TB data.
>
> Thank you in advance.
>
>
There's a good article on the AWS blog on multi tenancy with postgres:

https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

This is similar to Laurenz's second suggestion.

-Michel


> Regards,
> Vasu Madhineni
>
> On Fri, Jun 5, 2020 at 6:09 PM Rob Sargent  wrote:
>
>>
>>
>> On Jun 5, 2020, at 2:54 AM, Vasu Madhineni 
>> wrote:
>>
>> 
>> If the data size is more than 6TB, which approach better?
>>
>> On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe 
>> wrote:
>>
>>> On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote:
>>> > We are planning a POC on multitenant architecture in Postgres, Could
>>> you please
>>> > help us with steps for multitenant using schema for each application
>>> model.
>>>
>>> For few tenants, you can keep identical tables in several schemas and
>>> set "search_path" to select a tenant.
>>>
>>> With many tenants, you are better off with one table that holds the
>>> data for all clients.  You can use Row Level Security to have each
>>> tenant see only his or her data, and it might be a good idea to
>>> use list partitioning on the tenant ID.
>>>
>>> Yours,
>>> Laurenz Albe
>>> --
>>> Cybertec | https://www.cybertec-postgresql.com
>>>
>>> The question is How many separate data owners?
>>
>

Re: When to use PARTITION BY HASH?

[Michel Pelletier]

On Wed, Jun 3, 2020 at 4:55 AM Oleksandr Shulgin <
oleksandr.shul...@zalando.de> wrote:

>
> Do you also assign the partitions to different tablespaces as you've
> hinted below or do you see performance improvement from partitioning
> alone?  How does that work?  Does it give better  results than using a RAID
> to spread the disk IO, for example?
>

In general you could find write throughput improvements from all three,
partitioning, tablespacing, and disk striping.  It depends on your problem.
  Hash partitioning is a common feature in other databases as well. The
hash strategy works for many distributed access patterns.


> Yes its' true you end up with more smaller tables than one big large one,
>> but remember the indexes are (often) tree data structures.  Smaller trees
>> are faster than bigger trees.  By making the indexes smaller they are
>> faster.  Since the planner can knows to only examine the specific index it
>> needs, this ends up being a lot faster.
>>
>
> That sounds logical, but can it be demonstrated?  If the index(es) fit in
> memory fully, it doesn't make a measurable difference, I guess?
>

Well lets take a step back here and look at the question, hash partitioning
exists in Postgres, is it useful?  While I appreciate the need to see a
fact demonstrated, and generally avoiding argument by authority, it is true
that many of the very smartest database people in the world conceived of,
discussed, implemented and documented this feature for us.   It stands to
reason that it is useful, or it wouldn't exist.  So maybe this is more
about finding or needing better partitioning documentation.


> With hash partitioning you are not expected, in general, to end up with a
> small number of partitions being accessed more heavily than the rest.  So
> your indexes will also not fit into memory.
>

Indexes are not (usually) constant time structures, they take more time the
bigger they get.  So partitioned indexes will be smaller, quicker to insert
into, and quicker to vacuum, and also gain possible pruning advantages on
query when you split them up.  If the planner can, knowing the key, exclude
all but one partition, it won't even look at the other tables, so if you
hash partition by primary key, you reduce the search space to 1/N
immediately.

Indexes with high update activity also suffer from a problem called "index
bloat" where spares "holes" get punched in the buckets of btree indexes
from updates and delete deletes.  These holes are minimized by vacuuming
but the bigger the index gets, the harder that process is to maintain.
Smaller indexes suffer less from index bloat, and remedying the situation
is easier because you can reindex partitions independently of each other.
Your not just reducing the query load to an Nth, you're reducing the
maintenance load.

I have the feeling that using a hash function to distribute rows simply
> contradicts the basic assumption of when you would think of partitioning
> your table at all: that is to make sure the most active part of the table
> and indexes is small enough to be cached in memory.
>

I think you might be framing this with a specific data pattern in mind, not
all data distributions have a "most active" or power law distribution of
data.  For example i work with a lot of commercial airline position data
that services both real-time queries and ad-hoc analytical queries over
arbitrary airframe identifiers.   There is no advantage trying to have a
"most active" data strategy because all airframes in the air at any given
time are by definition most active.   A medium sized drone may send out as
many pings as a jumbo jet in a given interval of time.


-Michel


>
> Regards,
> --
> Alex
>
>

Re: When to use PARTITION BY HASH?

[Michel Pelletier]

On Tue, Jun 2, 2020 at 10:17 AM Oleksandr Shulgin <
oleksandr.shul...@zalando.de> wrote:

> Hi!
>
> I was reading up on declarative partitioning[1] and I'm not sure what
> could be a possible application of Hash partitioning.
>
> Is anyone actually using it?  What are typical use cases?  What benefits
> does such a partitioning scheme provide?
>
> On its face, it seems that it can only give you a number of tables which
> are smaller than the un-partitioned one, but I fail to see how it would
> provide any of the potential advantages listed in the documentation.
>

I'm sure there will be many delightful answers to your question, and I look
forward to them!  From my point of view, hash partitioning is very useful
for spreading out high insert/update load.  Yes its' true you end up with
more smaller tables than one big large one, but remember the indexes are
(often) tree data structures.  Smaller trees are faster than bigger trees.
By making the indexes smaller they are faster.  Since the planner can knows
to only examine the specific index it needs, this ends up being a lot
faster.

Postgres can also parallelize queries on partitions.  This is different
from a parallel sequential scan, which can also happen per-partition, so
there are multiple levels of parallel opportunity.

And last that I can think of, you can put the different partitions in
different tablespaces, improving the total IO bandwidth.

-Michel



> With a reasonable hash function, the distribution of rows across
> partitions should be more or less equal, so I wouldn't expect any of the
> following to hold true:
> - "...most of the heavily accessed rows of the table are in a single
> partition or a small number of partitions."
> - "Bulk loads and deletes can be accomplished by adding or removing
> partitions...",
> etc.
>
> That *might* turn out to be the case with a small number of distinct
> values in the partitioning column(s), but then why rely on hash
> assignment instead of using PARTITION BY LIST in the first place?
>
> Regards,
> --
> Alex
>
> [1] https://www.postgresql.org/docs/12/ddl-partitioning.html
>
>

Re: PG server process can keep some info of backend

[Michel Pelletier]

Not sure exactly what you're asking for, but perhaps check out
https://www.postgresql.org/docs/current/monitoring.html

On Fri, May 29, 2020 at 12:58 AM brajmohan saxena 
wrote:

>
> Hi,
>
> Is there any extension or option in PG to keep information of any (
> memory context/some memory address) of backend process in sever/postmaster
> main process.
>
> Thanks
> Braj
>

Re: GPG signing

[Michel Pelletier]

On Thu, May 28, 2020 at 5:14 PM Marc Munro  wrote:

> On Wed, 2020-05-27 at 14:42 -0700, Michel Pelletier wrote:
> > Hi Marc,
> >
> > You can sign content with pgsodium:
> >
> > https://github.com/michelp/pgsodium
>
> Michel,
> Yay!  A modern crypto implementation.  And it seems to do most of what
> I need right out of the box with way less work than pgcrypto.
>
> Any chance that crypto_sign_detatched() and
> crypto_sign_verify_detatched() will be implemented soon?
>
> I'll implement it and provide a patch if you'd like.
>

Yes please!  Always happy to merge a PR.

-Michel


> __
> Marc
>
>
>

Re: GPG signing

[Michel Pelletier]

As it's not well documented yet (sorry) I'm following up to add signing is
done with `crypto_sign()` and `crypto_sign_open()`

https://github.com/michelp/pgsodium/blob/master/test.sql#L73

On Wed, May 27, 2020 at 2:42 PM Michel Pelletier 
wrote:

> Hi Marc,
>
> You can sign content with pgsodium:
>
> https://github.com/michelp/pgsodium
>
> On Tue, May 26, 2020 at 12:21 PM Marc Munro  wrote:
>
>> On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote:
>> > On 5/26/20 12:01 PM, Marc Munro wrote:
>> > > I need to be able to cryptographically sign objects in my database
>> > > using a public key scheme.
>> > > [ . . . ]
>> > > Any other options?  Am I missing something?
>> >
>> > https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7
>>
>> I looked at that but I must be missing something.  In order to usefully
>> sign something, the private, secret, key must be used to encrypt a
>> disgest of the thing being signed (something of a simplification, but
>> that's the gist).  This can then be verified, by anyone, using the
>> public key.
>>
>> But the pgcrypto functions, for good reasons, do not allow the private
>> (secret) key to be used in this way.  Encryption and signing algorithms
>> are necessarily different as the secret key must be protected; and we
>> don't want signatures to be huge, and it seems that pgcrypto has not
>> implemented signing algorithms.
>>
>> What am I missing?
>>
>> __
>> Marc
>>
>>
>>

Re: GPG signing

[Michel Pelletier]

Hi Marc,

You can sign content with pgsodium:

https://github.com/michelp/pgsodium

On Tue, May 26, 2020 at 12:21 PM Marc Munro  wrote:

> On Tue, 2020-05-26 at 12:04 -0700, Adrian Klaver wrote:
> > On 5/26/20 12:01 PM, Marc Munro wrote:
> > > I need to be able to cryptographically sign objects in my database
> > > using a public key scheme.
> > > [ . . . ]
> > > Any other options?  Am I missing something?
> >
> > https://www.postgresql.org/docs/12/pgcrypto.html#id-1.11.7.34.7
>
> I looked at that but I must be missing something.  In order to usefully
> sign something, the private, secret, key must be used to encrypt a
> disgest of the thing being signed (something of a simplification, but
> that's the gist).  This can then be verified, by anyone, using the
> public key.
>
> But the pgcrypto functions, for good reasons, do not allow the private
> (secret) key to be used in this way.  Encryption and signing algorithms
> are necessarily different as the secret key must be protected; and we
> don't want signatures to be huge, and it seems that pgcrypto has not
> implemented signing algorithms.
>
> What am I missing?
>
> __
> Marc
>
>
>

Re: Firing an event trigger for ADD COLUMN?

[Michel Pelletier]

On Mon, May 11, 2020 at 5:23 PM Alvaro Herrera 
wrote:

> On 2020-May-11, Michel Pelletier wrote:
>
> > Is this asymmetry a bug?  I realize these event trigger functions are
> > typically written in C, but I'd like to keep this idea contained to a
> > plpgsql function and cover as many reversible changes as I can.
>
> Sadly, you cannot obtain all the necessary details using only plpgsql.
> The only way is to compile a C extension.  There's a very rough,
> incomplete skeleton to show how to do this in
> src/test/modules/test_ddl_deparse.  A much more complete approach, which
> was not completed, was used to produce JSON output from the C
> structures -- see
> https://www.postgresql.org/message-id/20150409161419.gc4...@alvh.no-ip.org


Ah thank you, test_ddl_deparse gives me a good idea what's going on now.
Very interesting thread, thanks for your work on this.

Keep in mind ALTER TABLE is a very sophisticated; you can do things like
> ALTER TABLE one ADD COLUMN two INT, DROP COLUMN three, ADD COLUMN four
> text;
> (i.e. do several things in one command) so if you were to use a
> simplistic approach, you might end up missing things.
>

This is a support feature for some users so they are encouraged to write
down scripts.  The idea being the tool would spit one out and they'll be
advised to use it as a starting point for the actual post-hoc down script,
so catching CREATE TABLE/INDEX is a good start.  As this ddl deparsing
feature evolves in the future, I can add more complex cases.

Thanks again!

-Michel


> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Firing an event trigger for ADD COLUMN?

[Michel Pelletier]

I'm writing a script that "reverses" ddl operations in an "up" script by
capturing event triggers and generating a "down" script for reverting.
I've got it working great for tables, indexes, etc, but it seems ADD COLUMN
doesn't sent an event, here's the code i'm using, pasted straight from the
documentation:

https://gist.github.com/michelp/c6daa1c123c4743be4f8b08fad5d5396

And here's the results I get, I get an event for dropping a column, but not
adding one:

postgres=# create table foo (bar int);
NOTICE:  CREATE TABLE created object: table public public.foo
CREATE TABLE
postgres=# alter table foo add column baz int;
NOTICE:  ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=# alter table foo drop column bar;
NOTICE:  ALTER TABLE dropped object: table column public.
public.foo.bar
NOTICE:  ALTER TABLE created object: table public public.foo
ALTER TABLE
postgres=#

Is this asymmetry a bug?  I realize these event trigger functions are
typically written in C, but I'd like to keep this idea contained to a
plpgsql function and cover as many reversible changes as I can.

Thank you,

-Michel

Re: Postgres for SQL Server users

[Michel Pelletier]

On Mon, May 6, 2019 at 2:49 PM Adam Brusselback 
wrote:

> I think the main "gotcha" when I moved from SQL Server to Postgres was I
> didn't even realize the amount of in-line t-sql I would use to just get
> stuff done for ad-hoc analysis. Postgres doesn't have a good way to emulate
> this. DO blocks cannot return resultsets, so short of creating a function
> and dropping it, it's not possible to get the same workflow.
>

Just ruminating here, and this has probably already been discussed in the
past, but I've always wanted something like a 'SELECT DO [LANGUAGE ...]
RETURNS rettype | TABLE (...) $$ RETURN [NEXT | QUERY] ... $$; but haven't
had any serious problem with creating/dropping functions like you mentioned.

-Michel


> The lack of GUI tooling was also a huge "whoa" moment for me, which I
> still grapple with.
>

Re: Looking for feedback and contributions

[Michel Pelletier]

On Sat, May 4, 2019 at 9:32 AM Bernard Quatermass <
toolsm...@quatermass.co.uk> wrote:

> Apologies if this isn’t the right place for this.
>
> I have created a helper daemon “jpigd”, FastCGI JSON Postgresql Gateway
>
> A tool to aid the elimination of CGI scripts on web servers and moving all
> the business functionality into the database server along with the rest the
> business storage logic.
>
>
> It is a fast-cgi handler that simply calls an SQL function with POST data
> as the first parameter and JSON encoded version of the CGI environment as
> the second parameter.
>
> A more thorough description can be found in the project README at
>
> https://gitlab.quatermass.co.uk/toolsmith/jpigd/blob/master/README.md
>
> The idea being that there is a whole class of applications that are
> primarily database interaction only and where traditional middleware
> (php/perl/java/node) is redundant.
>

Hi Bernard,

This looks cool!  I personally think it's a great pattern, and you can find
similar tools that take this approach to varying degrees.  Here's the ones
I know about.

http://postgrest.org/en/v5.2/

https://www.graphile.org/postgraphile/

https://hasura.io

The key is that they are all fairly "stateless", in that they don't contain
any application logic in themselves, but rely on the database for all
application functionality and only as as "connectors", loosely speaking,
from the network to the db.  Hasura is the only one I don't have much
experience with, so perhaps someone can correct me on that if I've got it
wrong.

Your tool looks much simpler and FastCGI specific, which is an advantage
I'm sure for your uses.  I encourage you to continue to elaborate the idea,
I think this entire toolspace is pretty ripe for innovation.

-Michel




>
> At this point I’m just looking for thoughts, comments, suggestions,
> possible sample apps (or suggestions for same), help with documenting the
> locked-down user approach.
>
> Thank you in advance for any feedback you can give.
>
> —
> Bernard Quatermass - The Toolsmith.
>

Re: how to add more than 1600 columns in a table?

[Michel Pelletier]

On Wed, Apr 24, 2019 at 3:11 PM pabloa98  wrote:

> We used tables because we have 2 types of queries on this table:
>
> SELECT * FROM table_wih_lots_of_columns WHERE condition involving a lot of
> columns.
> These type of queries read lot of rows.
>
> or
>
> SELECT columnX FROM table_wih_lots_of_columns WHERE condition involving a
> lot of columns
> These type of queries read very few rows.
>
>
Everyone else has had great advice on this, I'd like to add that arrays of
any dimension are limited to 1GB like all varlena objects.

You should check out pg-strom, it's highly optimized for running exactly
these kinds of queries on a GPU and comes with a native matrix type that
can exceed the 1GB limit.

http://heterodb.github.io/pg-strom/

-Michel

>
>>

Re: Quick hack for fetching the contents of a temp table

[Michel Pelletier]

On Tue, Apr 23, 2019 at 2:56 PM Souvik Bhattacherjee 
wrote:

> Hi,
>
> I need to use the return value of the function somefunction1() to modify a
> query and execute it.
>
> While it is easy to fetch the desired result using plpgsql functions(),
> however modifying and rewriting the query using the plpgsql does not seem
> straightforward. Therefore I have already written the query modification
> function within the postgres server since I need to use the results from
> pg_parse_query() method.
>
>
This question seems related to the other thread you started on April 18th?
Several of us suggested you use a CTE to materialize your intermediate
values.  Did you try that?  The example you provided was not complete, and
I asked for a reproducible example of what you were trying to achieve and
you didn't provide one.  I'm all for encouraging people to hack in the
core, but it seems like you have the impression that your goal is not
possible in SQL when it very likely is.  Changing the core is not only
going to be a high maintenance burden on you, but it also implies you have
a problem that no one else in 30 years of postgres development has had.
What's more likely is that it is possible to do what you want in SQL and
we'd like to help you, but we need more information and you need to try the
suggestions and answer the questions we ask back of you.

I'm happy to be wrong about this, because that means postgres can improve
in some way, but first we need more information about what your problem
actually is.

-Michel

Re: Primary key data type: integer vs identity

[Michel Pelletier]

On Fri, Apr 19, 2019 at 10:55 AM Rich Shepard 
wrote:

> When I created the database I set primary key data types as integer; for
> example:
>
> Column|   Type  | Collation | Nullable |  Default
> --+---+---+--+-
>   org_id   | integer |   | not null |
> nextval('organizations_org_id_seq'::regclass)
>
> I probably should have used the serial type, but didn't.
>
> If it would be advisable for me to convert from integer to identity please
> point me to the appropriate work flow.
>
>
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

"The data types smallserial, serial and bigserial are not true types, but
merely a notational convenience for creating unique identifier columns"

so really there's no difference, what you have is fine.

-Michel



Reading the CREATE TABLE pages in the manual did not give me sufficient
> insights to appreciate the differences or indicate how to change the
> column's data type.
>
> Regards,
>
> Rich
>
>
>

Re: Method to pass data between queries in a multi-statement transaction

[Michel Pelletier]

On Thu, Apr 18, 2019 at 9:06 AM Michael Lewis  wrote:

> Thus, what I'm looking for here is way to store the information and then
>> pass that information to the next query efficiently.
>> For example, is it possible to define a struct of my choice, private to
>> the current transaction, that would store the data and then pass it around
>> to the next query in the transaction without having to materialize that
>> struct (or deal with concurrency issues as in the hash table approach
>> mentioned earlier) .
>>
>
> Perhaps I am missing something obvious, but why not use a temp table?
>

Right, or as Adrian and I pointed out, use a CTE (WITH) query, which will
materialize any results you want for the query.  Perhaps if you give us a
working, reproducible, self contained example of what you expect we can
help you better.

Re: Method to pass data between queries in a multi-statement transaction

[Michel Pelletier]

On Wed, Apr 17, 2019 at 5:30 PM Souvik Bhattacherjee 
wrote:

> > What if I need the result of the join to be stored into table3 as well
> as the tuples that participated in the query to be deleted from table1. The
> following can be done without the need to transfer values from the previous
> query into the next:
>
> begin;
> insert into table3 (id, attr1, attr2) (select t1.cid, t1.empname, t2.dept
> from table1 t1, table2 t2 where t1.cid = t2.cid);
> delete from table1 where cid in (select c.cid from table1 t1, table2 t2
> where t1.cid = t2.cid);
> commit;
>
>

You can use INSERT...RETURNING in a WITH query:

postgres=# create table foo (a integer);
CREATE TABLE
postgres=# create table bar (b integer);
CREATE TABLE
postgres=# insert into bar values (42);
INSERT 0 1
postgres=# with i as (insert into foo values (42) returning a) delete from
bar where b = (select a from i);
DELETE 1

-Michel


>
> On Wed, Apr 17, 2019 at 6:16 PM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>>
>> On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee 
>> wrote:
>>
>>> Hello,
>>>
>>> I'm trying to pass some values between queries in a multi-statement
>>> transaction. For example, consider the following representative
>>> multi-statement transaction:
>>>
>>> begin;
>>> select * from table1 t1, table2 t2 where t1.cid = t2.cid;
>>> delete from table1 where cid in
>>> (values-to-be-populated-from-the-previous-query);
>>> commit;
>>>
>>
>> There are few if any situations where you need to immediately and
>> completely pass all values from one query to another in the same
>> transaction where the queries cannot just be combined into a single
>> statement.  Your representative example is one that is easily combined into
>> a single statement.
>>
>> Now, the stuff you are trying seems to indicate you are trying to do
>> something in C, inside the engine itself, with all of this.  If that is the
>> case you may want to be more clear as to what you are attempting to do.
>> But as far as server SQL goes the only persistence area are
>> tables/relations - including temporary ones.
>>
>> David J.
>>
>

Re: How to use full-text search URL parser to filter query results by domain name?

[Michel Pelletier]

On Wed, Apr 10, 2019 at 1:58 AM Jess Wren  wrote:

>->  Parallel Seq Scan on links
> (cost=0.00..4554.40 rows=75740 width=112)
>
>->  Function Scan on ts_parse  
> (cost=0.00..12.50 rows=5 width=32)
>  Filter: (tokid = 6)
> (23 rows)
>
>
>
> I am wondering if there is a more efficient way to do things? Some people
> on IRC mentioned that it might be better to declare a scalar function to
> return the host from ts_parse instead of the LATERAL query ... but I
> couldn't figure out how to do that, or if it was even preferable to the
> above from a performance standpoint ... any ideas on how I could improve
> the above.
>

May try indexing the parsed expression to avoid the seq scan on links,
something like:

create index on links (ts_parse('default', target));

and then run the explain (or explain analyze) to see if that improves
things.  Certainly as the links table gets bigger this should help.

Re: Pg analog to DBCC CCHECKDB?

[Michel Pelletier]

There is amcheck:

https://www.postgresql.org/docs/current/amcheck.html

"When the heapallindexed argument to verification functions is true, an
additional phase of verification is performed against the table associated
with the target index relation. This consists of a “dummy” CREATE INDEX
operation, which checks for the presence of all hypothetical new index
tuples against a temporary, in-memory summarizing structure (this is built
when needed during the basic first phase of verification). The summarizing
structure “fingerprints” every tuple found within the target index. The
high level principle behind heapallindexed verification is that a new index
that is equivalent to the existing, target index must only have entries
that can be found in the existing structure."

I haven't used this tool but it looks promising:

https://github.com/tvondra/pg_check


On Fri, Apr 5, 2019 at 7:34 AM Ron  wrote:

> Hi,
>
> In 9.6, does such a thing exist?  (We just restored a VM from snapshot and
> I
> want to verify the cluster sanity.)
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>
>

Re: Help with insert query

[Michel Pelletier]

Please reply-all to the list and not just me directly.

I didn't realize you meant the table was empty when you did the full
insert.  As Adrian pointed out, run your select using explain, it will show
you why you are producing no rows.  Looking at your query just
superficially, the outer join looks suspicious, maybe using a subqery to
get the ending balance is a better approach.




On Mon, Apr 1, 2019 at 11:02 AM Glenn Schultz  wrote:

> Hi Michael,
>
> I will try that.  What I don’t understand is why, when using just one loan
> the insert is successful but when working with the whole table once the
> query is done there is nothing inserted into the table.
>
> Best,
> Glenn
>
> Sent from my iPhone
>
>
> On Apr 1, 2019, at 1:55 PM, Michel Pelletier 
> wrote:
>
> On Mon, Apr 1, 2019 at 10:37 AM Glenn Schultz  wrote:
>
>> All,
>>
>> The query below is designed to insert into a table.  This works when I
>> have a single loan which I insert.  However, if remove the part of the
>> where clause of a single loan the insert does not work.  The table fnmloan
>> is a large table with 500mm + rows and the query runs for about 4 hours.
>> Any idea of how to get this to work?  I am a little stumped since the query
>> works with one loan.
>>
>>
> Inserting one row is fast, inserting 500 million rows is going to take
> quite a bit longer.  I suggest your break your query up into batches, and
> insert, say, 1 million rows at a time.  Also it might be a good idea to
> drop your indexes on the target table and re-create them after you do the
> bulk insert, and also do an 'ANALYZE' on the target table after you have
> inserted all the records.
>
> -Michel
>
>
>
>> Glenn
>>
>> SET max_parallel_workers_per_gather = 8;
>> SET random_page_cost = 1;
>>
>> truncate fnmloan_balance;
>> insert into fnmloan_balance (
>> fctrdt, loanseqnum, secmnem, beginbal, scheduled,endbal,smm
>> )
>>
>> select
>> fnmloan.fctrdt
>> ,fnmloan.loanseqnum
>> ,fnmloan.secmnem
>> --,fnmloan.orignoterate
>> --,fnmloan.loanage
>> --,fnmloan.origloanamt
>> ,fnmloan.currrpb as beginbal
>> ,round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
>>   cast(fnmloan.remterm - 1 as numeric),
>>   cast(fnmloan.currrpb as numeric)),4)) as scheduled
>> ,coalesce(endbal.currrpb,0) as endbal
>> ,abs(round(
>>   cast((fnmloan.currrpb - coalesce(endbal.currrpb,0) -
>> round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
>>   cast(fnmloan.remterm - 1 as numeric),
>>   cast(fnmloan.currrpb as numeric)),4)) )/(fnmloan.currrpb -
>> round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
>>   cast(fnmloan.remterm - 1 as numeric),
>>   cast(fnmloan.currrpb as numeric)),4)) ) as numeric)
>>   ,4)) as SMM
>>
>> from
>> (
>>  select * from fnmloan
>>  where
>>  fctrdt < '03-01-2019'
>>  and
>>  loanseqnum = '5991017042'
>> ) as fnmloan
>>
>>
>> left outer join
>> (select
>> fctrdt - interval '1 month' as fctrdt
>> ,loanseqnum
>> ,orignoterate
>> ,loanage
>> ,origloanamt
>> ,currrpb
>> from fnmloan
>> ) as endbal
>>
>> on fnmloan.loanseqnum = endbal.loanseqnum
>> and fnmloan.fctrdt = endbal.fctrdt
>>
>>

Re: Help with insert query

[Michel Pelletier]

On Mon, Apr 1, 2019 at 10:37 AM Glenn Schultz  wrote:

> All,
>
> The query below is designed to insert into a table.  This works when I
> have a single loan which I insert.  However, if remove the part of the
> where clause of a single loan the insert does not work.  The table fnmloan
> is a large table with 500mm + rows and the query runs for about 4 hours.
> Any idea of how to get this to work?  I am a little stumped since the query
> works with one loan.
>
>
Inserting one row is fast, inserting 500 million rows is going to take
quite a bit longer.  I suggest your break your query up into batches, and
insert, say, 1 million rows at a time.  Also it might be a good idea to
drop your indexes on the target table and re-create them after you do the
bulk insert, and also do an 'ANALYZE' on the target table after you have
inserted all the records.

-Michel



> Glenn
>
> SET max_parallel_workers_per_gather = 8;
> SET random_page_cost = 1;
>
> truncate fnmloan_balance;
> insert into fnmloan_balance (
> fctrdt, loanseqnum, secmnem, beginbal, scheduled,endbal,smm
> )
>
> select
> fnmloan.fctrdt
> ,fnmloan.loanseqnum
> ,fnmloan.secmnem
> --,fnmloan.orignoterate
> --,fnmloan.loanage
> --,fnmloan.origloanamt
> ,fnmloan.currrpb as beginbal
> ,round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
>   cast(fnmloan.remterm - 1 as numeric),
>   cast(fnmloan.currrpb as numeric)),4)) as scheduled
> ,coalesce(endbal.currrpb,0) as endbal
> ,abs(round(
>   cast((fnmloan.currrpb - coalesce(endbal.currrpb,0) -
> round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
>   cast(fnmloan.remterm - 1 as numeric),
>   cast(fnmloan.currrpb as numeric)),4)) )/(fnmloan.currrpb -
> round(nullif(schedprin(cast(fnmloan.orignoterate as numeric),
>   cast(fnmloan.remterm - 1 as numeric),
>   cast(fnmloan.currrpb as numeric)),4)) ) as numeric)
>   ,4)) as SMM
>
> from
> (
>  select * from fnmloan
>  where
>  fctrdt < '03-01-2019'
>  and
>  loanseqnum = '5991017042'
> ) as fnmloan
>
>
> left outer join
> (select
> fctrdt - interval '1 month' as fctrdt
> ,loanseqnum
> ,orignoterate
> ,loanage
> ,origloanamt
> ,currrpb
> from fnmloan
> ) as endbal
>
> on fnmloan.loanseqnum = endbal.loanseqnum
> and fnmloan.fctrdt = endbal.fctrdt
>
>

Re: Gigantic load average spikes

[Michel Pelletier]

On Sun, Mar 31, 2019 at 10:49 PM David Rowley 
wrote:

>
> Perhaps a bunch of processes waiting on the access exclusive lock on
> the materialized view being released?
>
> log_lock_waits might help you if the MV takes more than a second to
> refresh, otherwise, you might need to have a look at ungranted locks
> in pg_locks and see if the number of locks spikes during the refresh.
>

I think David's got the right idea here.  Like he said, investigate
pg_locks, if it is the refresh materialized view, you can avoid the problem
by doing 'REFRESH MATERIALIZED VIEW CONCURRENTLY'.  You will need at least
one unique index on the table.


> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
>

Re: Table Export & Import

[Michel Pelletier]

On Mon, Apr 1, 2019 at 7:47 AM Sathish Kumar  wrote:

> Hi Adrian,
> We are exporting live table data to a new database, so we need to stop our
> application until the export/import is completed. We would like to minimise
> this downtime.
>

It's more complicated if you want to keep your application running and
writing to the db while migrating.  There are trigger-level replication
tools, like slony that can be used to stream changes to the new database,
and then you switch over once you get both of them to parity, but there are
some gotchas.  You said the db is only 160GB, it depend a lot on what kind
of schema we're talking about, but I imagine it wouldn't take long to just
take the downtime and do a normal pg_upgrade.


>
> On Mon, Apr 1, 2019, 10:22 PM Adrian Klaver 
> wrote:
>
>> On 3/31/19 11:09 PM, Sathish Kumar wrote:
>> > Hi Team,
>> >
>> > We have a requirement to copy a table from one database server to
>> > another database server. We are looking for a solution to achieve this
>> > with lesser downtime on Prod. Can you help us with this?
>>
>> So what is creating the downtime now?
>>
>> In addition to other suggestions you might want to take a look at:
>>
>> https://www.postgresql.org/docs/9.5/postgres-fdw.html
>>
>>
>> >
>> > Table Size: 160GB
>> > Postgresql Server Version: 9.5
>> >
>> >
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>

Re: Table Export & Import

[Michel Pelletier]

As other have pointed out, you can take a pg_dump at anytime.  You can
provide arguments to pg_dump to only dump a subset of the database (like
one table).  Also mentioned is using a foreign data wrapper (FDW).  yet
another approach is to use the "copy to/from program" command to stream the
table from one db to the other using netcat (nc):

On destination server:

\copy table_name to program "nc origin_server ";

On origin server:

\copy table_name from program ''nc -l ";

If your network is slower than your cpu you can compress the data by piping
it through lz4 (or gzip/bzip whatever, but lz4 is fast both directions for
streaming).

-Michel


On Sun, Mar 31, 2019 at 11:10 PM Sathish Kumar  wrote:

> Hi Team,
>
> We have a requirement to copy a table from one database server to another
> database server. We are looking for a solution to achieve this with lesser
> downtime on Prod. Can you help us with this?
>
> Table Size: 160GB
> Postgresql Server Version: 9.5
>
>
>

Re: Key encryption and relational integrity

[Michel Pelletier]

On Tue, Mar 26, 2019 at 9:39 AM Kevin Brannen  wrote:

> -Original Message-
> From: Moreno Andreo 
> Sent: Tuesday, March 26, 2019 11:09 AM
> To: Adrian Klaver ; PostgreSQL mailing lists <
> pgsql-gene...@postgresql.org>
> Subject: Re: Key encryption and relational integrity
>
> In a master-detail relation, I need to encrypt one of master table PK or
> detail table FK, in order to achieve pseudonimization, required by GDPR in
> Europe when managing particular data Imagine I have Table users id
>  surnamelast name
> 1JohnDoe
> 2JaneDoe
> 3Foo Bar
>
> Table medications
> iduser_idmed
> 1 1Medication
> 2 1Ear check
> ...
> ...
> medications.user_id is FK on users.id
> we should achieve
>
> Table medications
> iduser_idmed
> 1sgkighs98Medication
> 2sghighs98Ear check
>
> or the opposite (users.id encryption and medications.user_id kept plain)
>
> At a first glance, it IS breaking relational integrity, so is there a way
> to manage this encryption internally so RI is kept safe?
>
> ===
>
> To me, this is really more of an application question, not a DB question.
> When you dump or share the data, do your randomization then. If you want to
> do it in the DB, then add an extra column to your user table, let's call it
> "pseudonym" and when you insert a new user, fill that in with your
> randomization string. Then never dump or use the real name, but use the
> pseudonym column. Better if you can do it, just don't store the real name
> (though your application may not let you do that).
>
>
Agreed, OP could also encrypt the name columns with pgcrypto and keep the
key out of the database.

-Michel

Re: printing JsonbPair values of input JSONB on server side?

[Michel Pelletier]

For server crashes you can't beat gdb in my opinion.  It's a challenge but
worth it in the long run to have gdb skills if you're coding in C (or
Python, since pdb shares many of gdb's keybindings).

But just looking at the function I don't see what's immediately wrong,
what's your CREATE FUNCTION statement look like?

On Tue, Mar 19, 2019 at 1:08 PM T L  wrote:

> Thanks a lot for the hint. I've used the iteration style and cleaned up
> the code as far as I can.
> It now correctly prints the keys and values, but the server crashes near
> function return.
>
> Any suggestions?
>
> -- function code --
>
> PG_FUNCTION_INFO_V1(print_kv_pair);
> Datum
> print_kv_pair(PG_FUNCTION_ARGS)
> {
> //1. extracting JsonbValue
> Jsonb *jb = PG_GETARG_JSONB_P(0);
> JsonbIterator *it;
> JsonbValuev;
> JsonbIteratorToken r;
> JsonbParseState *state = NULL;
>
> if (jb == NULL)
> PG_RETURN_BOOL(false);
>
> if (!JB_ROOT_IS_OBJECT(jb))
> ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("Can only take objects")));
>
> it = JsonbIteratorInit(&jb->root);
> r = JsonbIteratorNext(&it, &v, false);
> if (r != WJB_BEGIN_OBJECT)
> ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("Iterator was not an object")));
>
> //2. iterating through key-value pairs
> char *buf;
> while ((r = JsonbIteratorNext(&it, &v, true)) != WJB_DONE)
> {
> switch (r) {
> case WJB_KEY:
> buf = pnstrdup(v.val.string.val, v.val.string.len);
> elog(NOTICE, "print_kv_pair(): k = %s", buf);  //debug
> break;
> case WJB_VALUE:
> if (v.type != jbvNumeric) {
> ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
> errmsg("value must be numeric")));
> }
> elog(NOTICE, "print_kv_pair(): v = %s",
> DatumGetCString(DirectFunctionCall1(numeric_out,
> NumericGetDatum(v.val.numeric))) ); //debug
> break;
> case WJB_END_OBJECT:
> break;
> default:
> elog(ERROR, "invalid JsonbIteratorNext rc: %d", (int ) r);
> }
> }
> elog(NOTICE, "print_kv_pair(): ok4");
>
> PG_RETURN_BOOL(true);
> }
>
> -- output --
>
> => select print_kv_pair('{"a":1, "b": 2}');
> NOTICE:  print_kv_pair(): k = a
> NOTICE:  print_kv_pair(): v = 1
> NOTICE:  print_kv_pair(): k = b
> NOTICE:  print_kv_pair(): v = 2
> NOTICE:  print_kv_pair(): ok4
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
> !>
>
>
> On Tue, Mar 19, 2019 at 2:22 PM Michel Pelletier <
> pelletier.mic...@gmail.com> wrote:
>
>> jsonb_each is a wrapper around each_worker_jsonb.  It produces a row for
>> every key/value pair in an object.
>>
>>
>> https://doxygen.postgresql.org/jsonfuncs_8c.html#a7511a3aa3918eb956f3f4211d07bdbb0
>>
>> the iteration is:
>>
>>   while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)
>>
>>
>>
>> On Tue, Mar 19, 2019 at 11:20 AM T L  wrote:
>>
>>> I need this in my C code on the server side. Any link to the
>>> `jsonb_each` for this? Examples I found in a quick search are on the client
>>> side in SQL.
>>>
>>> I am just confused about the various jsonb types and how to effectively
>>> extract values and convert between them:
>>>
>>> There are Jsonb, JsonbValue (plus the associated JsonbPair ) to begin
>>> with. The ` JsonbToCStringWorker ` example that Andrew pointed out uses
>>> still another "JsonbContainer" type.
>>> But the type I get from "PG_GETARG_JSONB_P" is Jsonb. And it doesn't fit
>>> into " JsonbContainer" or the pointer math about "JsonPair" that I found
>>> online.
>>>
>>> What I am struggling with adapting some of the iterator code I saw is
>>> how to delete irrelevant code without breaking it. My use case is very
>>> restricted and handles hstore-like jsonb's.
>>> I don't need or want the code to have the ability to descend into nested
>>> objects or handle arrays etc., as they are invalid input in my case.
>>>
>>> I thought the pointer math example I found is eas

Re: printing JsonbPair values of input JSONB on server side?

[Michel Pelletier]

jsonb_each is a wrapper around each_worker_jsonb.  It produces a row for
every key/value pair in an object.

https://doxygen.postgresql.org/jsonfuncs_8c.html#a7511a3aa3918eb956f3f4211d07bdbb0

the iteration is:

  while ((r = JsonbIteratorNext(&it, &v, skipNested)) != WJB_DONE)



On Tue, Mar 19, 2019 at 11:20 AM T L  wrote:

> I need this in my C code on the server side. Any link to the `jsonb_each`
> for this? Examples I found in a quick search are on the client side in SQL.
>
> I am just confused about the various jsonb types and how to effectively
> extract values and convert between them:
>
> There are Jsonb, JsonbValue (plus the associated JsonbPair ) to begin
> with. The ` JsonbToCStringWorker ` example that Andrew pointed out uses
> still another "JsonbContainer" type.
> But the type I get from "PG_GETARG_JSONB_P" is Jsonb. And it doesn't fit
> into " JsonbContainer" or the pointer math about "JsonPair" that I found
> online.
>
> What I am struggling with adapting some of the iterator code I saw is how
> to delete irrelevant code without breaking it. My use case is very
> restricted and handles hstore-like jsonb's.
> I don't need or want the code to have the ability to descend into nested
> objects or handle arrays etc., as they are invalid input in my case.
>
> I thought the pointer math example I found is easier to adapt, but I
> couldn't get a valid "JsonbPair" from the input parameter to feed into the
> pointer math.
>
>
>
>
>
>
> On Tue, Mar 19, 2019 at 9:50 AM Michel Pelletier <
> pelletier.mic...@gmail.com> wrote:
>
>> Yeah I'm not sure why you're looping using pointer math, the iterators
>> are there to provide that service.  Another function to check out
>> 'jsonb_each', other than the set returning function parts, it does what it
>> looks like your are trying to do.
>>
>> -Michel
>>
>> On Mon, Mar 18, 2019 at 4:12 PM Andrew Gierth <
>> and...@tao11.riddles.org.uk> wrote:
>>
>>> >>>>> "T" == T L  writes:
>>>
>>>  T> Below is my test. It prints a strange character instead of "a"; and
>>>  T> says that the value isn't numeric.
>>>
>>> Yeah, there's plenty else wrong with your code.
>>>
>>> Did you look at how JsonbToCStringWorker does it? that looks like the
>>> best example I can find on a quick scan.
>>>
>>> --
>>> Andrew (irc:RhodiumToad)
>>>
>>>

Re: printing JsonbPair values of input JSONB on server side?

[Michel Pelletier]

Yeah I'm not sure why you're looping using pointer math, the iterators are
there to provide that service.  Another function to check out 'jsonb_each',
other than the set returning function parts, it does what it looks like
your are trying to do.

-Michel

On Mon, Mar 18, 2019 at 4:12 PM Andrew Gierth 
wrote:

> > "T" == T L  writes:
>
>  T> Below is my test. It prints a strange character instead of "a"; and
>  T> says that the value isn't numeric.
>
> Yeah, there's plenty else wrong with your code.
>
> Did you look at how JsonbToCStringWorker does it? that looks like the
> best example I can find on a quick scan.
>
> --
> Andrew (irc:RhodiumToad)
>
>

Re: Conditional INSERT

[Michel Pelletier]

You're right it probably does, unless the constraint needs to do a
sub-query to get the matching pattern, which would require a trigger.

On Fri, Mar 15, 2019 at 12:05 PM Rob Sargent  wrote:

>
>
> On Mar 15, 2019, at 12:59 PM, Adrian Klaver 
> wrote:
>
> On 3/15/19 11:54 AM, basti wrote:
>
> this is a dns database, and the client is update the _acme-challenge for
> LE certificates. I don't want that the client can insert "any" txt record.
> the client should only insert data if the hostname start with
> _acme-challenge. i have no control on client.
> i have try this rule but the server reject this with a endless loop:
>
>
> To borrow a quote:
>
> "I had a problem so I decided to use a rule, now I have two problems."
>
> Do not use a rule. As suggested upstream use a BEFORE INSERT trigger, you
> will be a lot happier.
>
> CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
> WHERE NEW.hostname like '_acme-challenge%'
> DO INSERT INTO t_dnsadmin_records_txt VALUES (
> NEW.domainid,
> NEW.hostname,
> NEW.txtdata
> );
> On 15.03.19 19:17, Michael Lewis wrote:
>
> On Fri, Mar 15, 2019 at 10:55 AM basti  >
> wrote:
>
> Hello,
>
> I want to insert data into table only if condition is true.
> For example:
>
> INSERT into  mytable (domainid, hostname, txtdata)
>   VALUES (100,'_acme.challenge.example', 'somedata');
>
>  Alternative to a trigger implementation, if you are generating that
> INSERT statement, you can change it to use a sub-select or CTE that
> contains no values if the domainid isn't what you like. If you want it
> to fail with error, you could add a check constraint. We might need more
> context on what you are doing and why to give good advice.
>
>
>
>
> Does a check constraint not suffice in this situation?
>
>

Re: Conditional INSERT

[Michel Pelletier]

Well, the obvious question is, why are you inserting data into your
database you don't want?  It makes more sense to just not do the insert.

But, assuming perhaps you have no control over the client, you can create a
BEFORE INSERT trigger that rejects the inserts that don't match your
condition:

https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER



On Fri, Mar 15, 2019 at 10:55 AM basti  wrote:

> Hello,
>
> I want to insert data into table only if condition is true.
> For example:
>
> INSERT into  mytable (domainid, hostname, txtdata)
>   VALUES (100,'_acme.challenge.example', 'somedata');
>
> The insert should only be done if Hostname like %_acme.challenge%.
>
> How can it be done? I dont want that the user/script can insert any value.
>
> Best regards.
>
>

Re: jsonb_set performance degradation / multiple jsonb_set on multiple documents

[Michel Pelletier]

I don't know the details of jsonb_set, Perhaps the '||' operator will
perform better for you, it will overwrite existing keys, so you can build
your new values in a new object, and then || it to the original.

postgres=# select '{"a": 1, "b": 2, "c": 3}'::jsonb || '{"b": 4, "c":
5}'::jsonb;
 ?column?
--
 {"a": 1, "b": 4, "c": 5}
(1 row)

-Michel



On Fri, Mar 15, 2019 at 9:02 AM Alexandru Lazarev <
alexandru.laza...@gmail.com> wrote:

> Hi PostgreSQL Community.
>
> I tried to rewrite some plv8 stored procedures, which process in bulk
> JSONB documents, to PL/pgSQL.
> A SP usually has to delete/update/add multiple key with the same document
> and do it for multiple documents (~40K) in loop.
>
> When updating a single key PL/pgSQL wins against plv8, but when I need to
> update multiple keys with *jsonb_set*, timing increase linearly with
> number of *jsonb_set*s and takes longer than similar SP in PLV8.
> Below are test-cases I've used.
>
> *QUESTION:* Is it expected behavior or I do something wrong or there are
> some better approaches or we can treat datum as object?
>
> test case:
> PG 9.6, CentOS 7
>
> CREATE TABLE public.configurationj2b
> (
>   id integer NOT NULL PRIMARY KEY,
>   config jsonb NOT NULL
> );
> Each jsonb column has 3 top keys, and one of top-key ('data') has another
> 700-900 key-value pairs e.g. {"OID1":"Value1"}
>
> PL/pgSQL SP
> CREATE OR REPLACE FUNCTION public.process_jsonb()
>   RETURNS void AS
> $BODY$
> DECLARE
> r integer;
> cfg jsonb;
> BEGIN
> RAISE NOTICE 'start';
> FOR r IN
> SELECT id as device_id FROM devices
> LOOP
> select config into cfg from configurationj2b c where c.id = r;
> --select jsonb one by one
>
> -- MULTIPLE KEYs, Conditional Busiines Logic (BL) updates
> *cfg := jsonb_set(cfg, '{data,OID1}', '"pl/pgsql1"');*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *IF cfg@>'{"data" : { "OID1":"pl/pgsql1"} }' THENcfg :=
> jsonb_set(cfg, '{data,OID2}', '"pl/pgsql2"');END IF;IF
> cfg@>'{"data" : { "OID2":"pl/pgsql2"} }' THENcfg := jsonb_set(cfg,
> '{data,OID3}', '"pl/pgsql3"');END IF;IF cfg@>'{"data" : {
> "OID3":"pl/pgsql3"} }' THENcfg := jsonb_set(cfg, '{data,OID4}',
> '"pl/pgsql4"');END IF;IF cfg@>'{"data" : { "OID4":"pl/pgsql4"} }'
> THENcfg := jsonb_set(cfg, '{data,OID5}', '"pl/pgsql5"');END IF;*
>
> update configurationj2b c set config = cfg where c.id = r;
>
> END LOOP;
> RAISE NOTICE 'end';
> RETURN;
> END
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
>
> or in pseudo-code I would have
>
> for-each child_jsonb do
> begin
>   foreach (key-value in parent_jsonb) do
>   begin
> *child_jsonb  := jsonb_set(child_jsonb , '{key}', '"value"');*
>   end
>   update *child_jsonb * in db;
> end;
>
> plv8 snippet:
> $BODY$var ids = plv8.execute('select id from devices');
>
> var CFG_TABLE_NAME = 'configurationj2b';
> var selPlan = plv8.prepare( "select c.config from " + CFG_TABLE_NAME + " c
> where c.id = $1", ['int'] );
> var updPlan = plv8.prepare( 'update ' + CFG_TABLE_NAME + ' set config = $1
> where id = $2', ['json','int'] )
>
> try {
>
> for (var i = 0; i < ids.length; i++) {
> var db_cfg = selPlan.execute([ids[i].id]);
> var cfg = db_cfg[0].config;
> var cfg_data = cfg['data'];
> *cfg_data['OID1'] = 'plv8_01';*
>
>
>
>
>
>
>
>
>
>
>
> *if (cfg_data['OID1'] == 'plv8_01') {cfg_data['OID2']
> = 'plv8_02'};if (cfg_data['OID2'] == 'plv8_02') {
> cfg_data['OID3'] = 'plv8_03'}if (cfg_data['OID3'] ==
> 'plv8_03') {cfg_data['OID4'] = 'plv8_04'}if
> (cfg_data['OID4'] == 'plv8_04') {cfg_data['OID5'] =
> 'plv8_05'}*
>
> updPlan.execute([cfg, ids[i].id]);
> plv8.elog(NOTICE, "UPDATED = " + ids[i].id);
> }
>
> } finally {
> selPlan.free();
> updPlan.free();
> }
>
> return;$BODY$
>
> but for now plv8 has other issues related to resource consumption.
>
> So could I get similar performance in PL/pgSQL?
>

varlena objects greater than 1GB

[Michel Pelletier]

Hello,

I have read through this thread started by pg-strom's Kohei KaiGai:

https://www.postgresql.org/message-id/CADyhKSUP0PM6odyoV27q8CFG6mxMQFKY4R7XLz2NsXCHuwRZKA%40mail.gmail.com

and have a similar need to Kohei for varlena objects greater than 1GB, in
my case, also vector/matrix objects stored by the pggraphblas extension.

Thinking through my options, I was wondering if it would be a good idea to
use large object support to store matrix backing data, perhaps first as a
varlena object up to the limit, and then switch (in the flatten_into
function for my types) to an object storing the oid of the large object
holding the matrix data?

I'm going to experiment with this approach, but first wanted to raise the
issue to shoot some holes in first before trying it.

Thanks!

-Michel

Re: support for JSON Web Token

[Michel Pelletier]

On Sat, Mar 2, 2019 at 4:09 PM Eugen Stan  wrote:

> Hi,
>
> I would like to advocate forJSON web token support in PostgreSQL.
>
> JWT tokens are used in a lot of web applications and I think there are
> some very nice use cases for passing the token down to PostgreSQL.
>

pgjwt author here.  While I do agree with you that a direct integration
would simplify a few things, the idea doesn't really bring enough benefit
to overcome some downsides.


> Some of the first thing that comes to mind is pairing it with row level
> security to implement authorization policies and data access directly in
> DB.
>

It's possible to do this now, tools like PostgREST and Postgraphile use a
pattern where they log in as an Anonymous user but when they get a valid
JWT, they SET ROLE to the correct user.  It's also possible to inspect jwt
claims in RLS policy checks via variables that are set on each query.  It's
worth noting that neither of these projects use pgjwt but rather use the
libraries that come with their runtimes.

But more abstractly how would this integration work?  Postgres does not
speak HTTP, you'd have to extend the client protocol.  That's gonna be a
huge hurdle since its a large change to a lot of code outside of postgres
itself.


> I've did a bit of research and I found some prior work in [pgjwt] and
> [postgres-jwt-prototype] .
>
> It seems to be pretty easy to support since most of the work is done:
> PostgreSQL has crypto support via pgcrypto and has native JSON handling
> functions.
>

pgcrypto only supports secret key signing with hmac, but it doesn't support
public key signing.  pgjwt was always just an expedient tool, for me at
least, to use as an early adopter of PostgREST.  It is nice to be able to
generate valid jwts from the db with a secret.  But for any more serious
use it's going to need not only work to pgcrypto or a gratuitous self-plug
for pgsodium (https://github.com/michelp/pgsodium) to support public key
signing and it's going to need some eyeballs from real security people.  I
wrote them and I personally wouldn't use pgjwt (other than trival key
generation) or pgsodium for production use without some more buy in from
serious security people.  That's huge hurdle #2.


> JWT is a widely used technology, especially in web applications.
>
> This is why I think PostgreSQL should adopt a JWT extension in contrib.
>
> I believe PostgreSQL will benefit, especially in the web apps use.
>
>
> What do you think?
>
> What would it take for a JWT extension to be added to postgresql contrib
> or the official postgresql extensions ?
>

I like your enthusiasm for the idea, but it has some serious hurdles
noted.  In the mean time, if you're idea is to get working quickly with
postgres and JWT, I would look at PostgREST and Postgraphile, and I hear
Hasura is quite cool too although I don't have any firsthand experience
with it.

-Michel


>
>
> Thanks,
>
> Eugen
>
> [pgjwt] https://github.com/michelp/pgjwt
>
> [postgres-jwt-prototype] https://github.com/larsar/postgres-jwt-prototype
>
>
>
>

Re: Where **not** to use PostgreSQL?

[Michel Pelletier]

Doh, sorry I missed your postscript!

On Thu, Feb 28, 2019 at 8:02 AM Nicolas Grilly 
wrote:

> Le jeu. 28 févr. 2019 à 16:48, Michel Pelletier <
> pelletier.mic...@gmail.com> a écrit :
>
>> Check out the RUM index extension, it adds ranking information to indexes
>> to speed up exactly the problem you pointed out:
>>
>> https://github.com/postgrespro/rum
>>
>
> I mentioned it at the end of my message:-)
>
> It would be great to have it integrated in the standard distribution.
>
>>

Re: Where **not** to use PostgreSQL?

[Michel Pelletier]

On Thu, Feb 28, 2019 at 4:50 AM Nicolas Grilly 
wrote:

> On Thu, Feb 28, 2019 at 1:24 PM Chris Travers 
> wrote:
>
>> 1.  a) TB-scale full text search systems.
>>  b) PostgreSQL's full text search is quite capable but not so
>> powerful that it can completely replace Lucene-based systems.  So you have
>> to consider complexity vs functionality if you are tying with other data
>> that is already in PostgreSQL.  Note further that my experience with at
>> least ElasticSearch is that it is easier to scale something built on
>> multiple PostgreSQL instances into the PB range than it is to scale
>> ElasticSearch into the PB range.
>>  c) Solr or ElasticSearch
>>
>
> One question about your use of PostgreSQL for a TB-scale full-text search
> system: Did you order search results using ts_rank or ts_rank_cd? I'm
> asking because in my experience, PostgreSQL full-text search is extremely
> efficient, until you need ranking. It's because the indexes don't contain
> the necessary information for ranking, and because of this the heap has to
> be consulted, which implies a lot of random IO.
>
>
Check out the RUM index extension, it adds ranking information to indexes
to speed up exactly the problem you pointed out:

https://github.com/postgrespro/rum

Re: How to add a new psql command ?

[Michel Pelletier]

You can also shell out to a command with \! and send data one way using a
combo of \gset and \setenv, then bundle up the whole "function" as a .sql
file you include when you want it with \i.  For example here's a snippet I
use to get the session pid, export it, then fire up another tmux pane
running gdb attached to my session and then continue:

select pg_backend_pid() pid \gset
\setenv PID :'pid'
\! tmux split-window -h
\! tmux send-keys 'gdb /usr/bin/postgres ' $PID  'C-m' 'cont' 'C-m'

This trick is also useful to export snapshot ids to subprocesses that need
read consistent views with each other.

-Michel


On Thu, Feb 7, 2019 at 3:03 AM Pavel Stehule 
wrote:

> Hi
>
> čt 7. 2. 2019 v 11:40 odesílatel Quentin Di-Fant 
> napsal:
>
>> Hello,
>>
>> I was wondering : is it possible to add directly a new psql command which
>> can be run anytime in our PostgreSQL interpreter like any other normal psql
>> commands ?
>>
>> I explain myself : in the PostgreSQL interpreter, when the command "\h"
>> or "\help" is written in the shell, we can see all the commands we have at
>> our disposal. We can also of course write our proper functions, in python
>> scripts for example, and run them direclty in one of our databases. But I
>> was wondering if it was possible to add directly a command, like \copy,
>> \select,  For example, let's say I create a new command called \resume
>> which will call my own-made python script, and I would want this command to
>> be part of the list of commands we see when we type \h.
>>
>> Is it even possible ? And if it's possible, how can I achieve that ?
>>
>> Thanks for your attention and I thank you in advance for all the
>> informations you will possibly give me.
>>
>
> It is not possible. psql is not designed to be enhanced.
>
> you can use psql variables for this purpose partially
>
> postgres=# \set x 'select 1;'
> postgres=# :x
> ┌──┐
> │ ?column? │
> ╞══╡
> │1 │
> └──┘
> (1 row)
>
>
>
>
>> Cordially.
>>
>

Re: Implementing an expanded object in C

[Michel Pelletier]

Thanks Andrew, sorry for my late reply it took me a while to get time to
analyze the issue.

You are of course correct, I had confusion around the create type
statement, but the good news is, I have figured out the final ins and outs
of creating an expanded type, and my matrix type can now be used as
variables plpgsql functions and works great!  Thanks to you and Tom and
everyone else who helped me get unblocked and making progress.

-Michel

On Sun, Jan 27, 2019 at 7:14 PM Andrew Gierth 
wrote:

> >>>>> "Michel" == Michel Pelletier  writes:
>
>  Michel> Replying to my own problem here, I guess this was a situation
>  Michel> where explaining it in detail revealed the problem to me. By
>  Michel> specifying my type is 'passedbyvalue'
>
> That cannot possibly be appropriate.
>
>  Michel> CREATE TYPE matrix (
>  Michel> internallength = 8,
>
> Your type is clearly not a fixed-length type, because fixed-length types
> cannot have expanded datums. A fixed-length type must contain its entire
> representation within the fixed length - it is not allowed to be a
> pointer to something else.
>
> --
> Andrew (irc:RhodiumToad)
>

Re: Implementing an expanded object in C

[Michel Pelletier]

Replying to my own problem here, I guess this was a situation where
explaining it in detail revealed the problem to me.  By specifying my type
is 'passedbyvalue' and 'alignment = double' it now works!

CREATE TYPE matrix (
internallength = 8,
input = matrix_in,
output = matrix_out,
passedbyvalue,
alignment = double
);

Thanks for being a sounding board.

-Michel

On Sun, Jan 27, 2019 at 8:59 AM Michel Pelletier 
wrote:

> Hello,
>
> Apologies in advance for the long question.  I've made a lot of progress
> on my GraphBLAS extension and getting close to having most of the API
> usefully exposed to postgres, but I'm been struggling with an issue related
> to when i switched to using an expanded representation of matrix types.
>
> I've tried to follow closely how arrays work, but the answer still eludes
> me.  The situation is slightly different in that where an array's flat
> representation is useful, a sparse matrix's flat form is an edge-list, so
> it's not useful unexpanded so I have a macro, PGGRB_GETARG_MATRIX(n) always
> returns the expanded form by checking VARATT_IS_EXTERNAL_EXPANDED_RW and
> then DatumGetEOHP if true, otherwise expanding from the flat representation:
>
>
> https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L203
>
> I also have a PGGRB_RETURN_MATRIX(m) macro that calls `return
> EOHPGetRWDatum(&(A)->hdr)`
>
>
> https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/pggraphblas.h#L140
>
> This chain of calls works for me in some cases, for example an operator
> function, 'matrix_mxm' which overloads the '*' operator, can be used to
> multiply two matrices:
>
> postgres=# select '{{0,1,2},{1,2,0},{4,5,6}}'::matrix *
> '{{0,1,2},{1,2,0},{4,5,6}}'::matrix;
>?column?
> --
>  {{0,1,2},{2,0,1},{20,30,24}}
> (1 row)
>
> Works great! Internally this was `matrix_out(matrix_mxm(matrix_in(),
> matrix_in()))` where the data flow fine both in and out of the functions.
> But I have another function, 'matrix_agg', that aggregates edges from a
> query into a matrix.  It builds and returns the result matrix similarly to
> matrix_mxm does and returns it using the same macro, but matrix_out's call
> to get the agregates final value segfaults.
>
> select matrix_agg(i, j, v) from edges;  -- segfaults in matrix_out at
> PG_GETARG_MATRIX(0)
>
> at
>
>
> https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L207
>
> Afaict matrix_agg and matrix_mxm are both creating and returning matrices
> the same way, using the same function to build them and the same macro that
> `return EOHPGetRWDatum(&(A)->hdr)`, but when matrix_out fetches the
> argument to print the result it bombs on the aggregate's final value.  The
> only salient different I can see if the agg's final function calls:
>
>   if (!AggCheckCallContext(fcinfo, &resultcxt)) {
> resultcxt = CurrentMemoryContext;
>   }
>
>   oldcxt = MemoryContextSwitchTo(resultcxt);
>   // do matrix creation stuff
>   MemoryContextSwitchTo(oldcxt);
>
>
> But even if I remove that and do not switch contexts, it still crashes the
> same way.
>
> It must be possible to return expanded objects from aggregates so I'm
> clearly doing something wrong.  The final function actually worked before I
> was using expanded representation and just using PG_RETURN_POINTER, but
> despite having all these clues I've been staring at this segfault in gdb
> for a couple of days now.
>
> Any pointers on this subject would be greatly appreciated!  I know someone
> else out there recently was working on an expanded object posted on the
> list, if you don't see this, I may reach out to you. :)
>
> -Michel
>
>
>

Implementing an expanded object in C

[Michel Pelletier]

Hello,

Apologies in advance for the long question.  I've made a lot of progress on
my GraphBLAS extension and getting close to having most of the API usefully
exposed to postgres, but I'm been struggling with an issue related to when
i switched to using an expanded representation of matrix types.

I've tried to follow closely how arrays work, but the answer still eludes
me.  The situation is slightly different in that where an array's flat
representation is useful, a sparse matrix's flat form is an edge-list, so
it's not useful unexpanded so I have a macro, PGGRB_GETARG_MATRIX(n) always
returns the expanded form by checking VARATT_IS_EXTERNAL_EXPANDED_RW and
then DatumGetEOHP if true, otherwise expanding from the flat representation:

https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L203

I also have a PGGRB_RETURN_MATRIX(m) macro that calls `return
EOHPGetRWDatum(&(A)->hdr)`

https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/pggraphblas.h#L140

This chain of calls works for me in some cases, for example an operator
function, 'matrix_mxm' which overloads the '*' operator, can be used to
multiply two matrices:

postgres=# select '{{0,1,2},{1,2,0},{4,5,6}}'::matrix *
'{{0,1,2},{1,2,0},{4,5,6}}'::matrix;
   ?column?
--
 {{0,1,2},{2,0,1},{20,30,24}}
(1 row)

Works great! Internally this was `matrix_out(matrix_mxm(matrix_in(),
matrix_in()))` where the data flow fine both in and out of the functions.
But I have another function, 'matrix_agg', that aggregates edges from a
query into a matrix.  It builds and returns the result matrix similarly to
matrix_mxm does and returns it using the same macro, but matrix_out's call
to get the agregates final value segfaults.

select matrix_agg(i, j, v) from edges;  -- segfaults in matrix_out at
PG_GETARG_MATRIX(0)

at

https://github.com/michelp/pggraphblas/blob/bug/eohsegfault/src/matrix.c#L207

Afaict matrix_agg and matrix_mxm are both creating and returning matrices
the same way, using the same function to build them and the same macro that
`return EOHPGetRWDatum(&(A)->hdr)`, but when matrix_out fetches the
argument to print the result it bombs on the aggregate's final value.  The
only salient different I can see if the agg's final function calls:

  if (!AggCheckCallContext(fcinfo, &resultcxt)) {
resultcxt = CurrentMemoryContext;
  }

  oldcxt = MemoryContextSwitchTo(resultcxt);
  // do matrix creation stuff
  MemoryContextSwitchTo(oldcxt);


But even if I remove that and do not switch contexts, it still crashes the
same way.

It must be possible to return expanded objects from aggregates so I'm
clearly doing something wrong.  The final function actually worked before I
was using expanded representation and just using PG_RETURN_POINTER, but
despite having all these clues I've been staring at this segfault in gdb
for a couple of days now.

Any pointers on this subject would be greatly appreciated!  I know someone
else out there recently was working on an expanded object posted on the
list, if you don't see this, I may reach out to you. :)

-Michel

Re: Varlena with recursive data structures?

[Michel Pelletier]

Hi Karl,

I'm going down this road myself.  In addition to the files Tom Lane pointed
out there is also some helpful documentation here:

https://www.postgresql.org/docs/current/storage-toast.html#STORAGE-TOAST-INMEMORY

On Wed, Jan 16, 2019 at 2:09 PM Sam Patterson  wrote:

> Hi all,
>
> I've recently started developing an extension for Postgres for which I'll
> need to create a new variable-length base type. The type will require a
> tree-like structure in order to parse sufficiently, which of course
> probably means having some sort of recursive data structure, like a struct
> that has members which are pointers to itself for child nodes. After doing
> some research, specifically looking at how other variable-length data types
> store their data, it seems almost all of them store the data in a binary
> representation, using bit masks and offsets etc in order to store/access
> the data whilst having an in-memory representation that's used to
> manipulate the data.
>
> I presume the purpose for using this approach is because all the data in a
> varlena type has to be contiguous, and the moment you start using pointers
> this is no longer possible. So my question is, given a structure that looks
> something like this,
>
> typedef struct Node
> {
> char *data;
> Node *left;
> Node *right;
> } Node;
>
> am I right in saying that I wouldn't be able to store that representation
> on-disk, but instead I'd have to transform it into some binary
> representation and back again when writing/reading respectively, are there
> any alternatives?
>
> Regards,
>
> Karl
>

Re: Question about MemoryContextRegisterResetCallback

[Michel Pelletier]

After absorbing some of the code you've pointed out I have a couple of
questions about my understanding before I start hacking on making expanded
matrices.

Serializing sparse matrices can be done with _expand/_build functions, and
the size is known, so I can implement the EOM_flatten_into_methods.  From
the array examples, it looks like accessor functions are responsible for
detecting and unflattening themselves, so I think I've got that understood.

Reading expandeddatum.h says "The format appearing on disk is called the
data type's "flattened" representation. since it is required to be a
contiguous blob of bytes -- but the type can have an expanded
representation that is not.  Data types must provide means to translate an
expanded representation back to flattened form."

It mentions "on disk" does this mean the flattened representation must be
binary compatible with what matrix_send emits?  They will likely be the
same now, so I can see this as a convenience, but is it a requirement?
Future matrix_send implementations may do some form of compressed sparse
row format, which would be inefficient for in-memory copies.

Thanks again,

-Michel

On Sun, Jan 13, 2019 at 10:51 AM Michel Pelletier <
pelletier.mic...@gmail.com> wrote:

> On Sun, Jan 13, 2019 at 9:30 AM Tom Lane  wrote:
>
>> I suppose what you're doing is returning a pointer to a GraphBLAS object
>> as a Datum (or part of a pass-by-ref Datum)?  If so, that's not going
>> to work terribly well, because it ignores the problem that datatype-
>> independent code is going to assume it can copy Datum values using
>> datumCopy() or equivalent logic.  More often than not, such copying
>> is done to move the value into a different memory context in preparation
>> for freeing the original context.  If you delete the GraphBLAS object
>> when the original context is deleted, you now have a dangling pointer
>> in the copy.
>>
>> We did invent some infrastructure awhile ago that could potentially
>> handle this sort of situation: it's the "expanded datum" stuff.
>> The idea here would be that your representation involving a GraphBLAS
>> pointer would be an efficient-to-operate-on expanded object.  You
>> would need to be able to serialize and deserialize that representation
>> into plain self-contained Datums (probably varlena blobs), but hopefully
>> GraphBLAS is capable of going along with that.  You'd still need a
>> memory context reset callback attached to each expanded object, to
>> free the associated GraphBLAS object --- but expanded objects are
>> explicitly aware of which context they're in, so at least in principle
>> that should work.  (I'm not sure anyone's actually tried to build
>> an expanded-object representation that has external resources, so
>> we might find there are some bugs to fix there.)
>>
>> Take a look at
>>
>> src/include/utils/expandeddatum.h
>> src/backend/utils/adt/expandeddatum.c
>> src/backend/utils/adt/array_expanded.c
>> src/backend/utils/adt/expandedrecord.c
>>
>>
> Ah I see, the water is much deeper here.  Thanks for the detailed
> explanation, expandeddatum.h was very helpful and I see now how
> array_expanded works.  If I run into any problems registering my callback
> in the expanded context I'll repost back.
>
> Thanks Tom!
>
> -Michel
>
>
>> regards, tom lane
>>
>

Re: Question about MemoryContextRegisterResetCallback

[Michel Pelletier]

On Sun, Jan 13, 2019 at 9:30 AM Tom Lane  wrote:

> I suppose what you're doing is returning a pointer to a GraphBLAS object
> as a Datum (or part of a pass-by-ref Datum)?  If so, that's not going
> to work terribly well, because it ignores the problem that datatype-
> independent code is going to assume it can copy Datum values using
> datumCopy() or equivalent logic.  More often than not, such copying
> is done to move the value into a different memory context in preparation
> for freeing the original context.  If you delete the GraphBLAS object
> when the original context is deleted, you now have a dangling pointer
> in the copy.
>
> We did invent some infrastructure awhile ago that could potentially
> handle this sort of situation: it's the "expanded datum" stuff.
> The idea here would be that your representation involving a GraphBLAS
> pointer would be an efficient-to-operate-on expanded object.  You
> would need to be able to serialize and deserialize that representation
> into plain self-contained Datums (probably varlena blobs), but hopefully
> GraphBLAS is capable of going along with that.  You'd still need a
> memory context reset callback attached to each expanded object, to
> free the associated GraphBLAS object --- but expanded objects are
> explicitly aware of which context they're in, so at least in principle
> that should work.  (I'm not sure anyone's actually tried to build
> an expanded-object representation that has external resources, so
> we might find there are some bugs to fix there.)
>
> Take a look at
>
> src/include/utils/expandeddatum.h
> src/backend/utils/adt/expandeddatum.c
> src/backend/utils/adt/array_expanded.c
> src/backend/utils/adt/expandedrecord.c
>
>
Ah I see, the water is much deeper here.  Thanks for the detailed
explanation, expandeddatum.h was very helpful and I see now how
array_expanded works.  If I run into any problems registering my callback
in the expanded context I'll repost back.

Thanks Tom!

-Michel


> regards, tom lane
>

Question about MemoryContextRegisterResetCallback

[Michel Pelletier]

Hello,

I'm working on an extension to wrap the GraphBLAS linear algebra package.
GraphBLAS provides a very flexible API over adjacency matrices for solving
graph problems.  I've got Matrix and Vector types wrapped, build
aggregators and extraction functions to pivot tables into matrices and
back, and may of the core operations are supported for just one of the 960
different semirings that GraphBLAS supports, but i'm making good progress
and with some advanced macro'ing I hope to provide complete API access.

This is no doubt the most complex bit of C wrapper I've done for postgres,
and I've run into a bit of a snag.  GraphBLAS objects are opaque handles
that have their own new/free functions.  After reading mmgr/README I have
registered a callback with CurrentMemoryContext during my aggregator
function that builds values.

https://github.com/michelp/pggraphblas/blob/master/src/matrix.c#L80

I've got tests that work very well, up until I declare a matrix or vector
in a plpgsql function.

https://github.com/michelp/pggraphblas/blob/master/test.sql#L103

When using these objects from a function, their free function seems be be
called prematurely, as GraphBLAS raises an error that the object isn't
initialized when it tries to compare two matrices with 'matrix_eq' (the
free function "uninitializes" a handle).  If I use CurTransactionContext
instead of CurrentMemoryContext, the function doesn't fail, but the server
segfaults on rollback.

For the brave and curious the test can reproduce the error, if you have
docker installed, just clone the repo and run './test.sh'.  (The first
build takes a while due to compiling GraphBLAS). Here's an example failure:

https://gist.github.com/michelp/1ba3cc79996b8d3a963d974224a78f2d

Obviously there is something I'm doing wrong about these callbacks,
thinking my free function is getting called immediately after the statement
that creates it, so I'm not sure what context to register it under.  Should
I create a new one?  Register it to the CurrentMemoryContext parent maybe?
Any help from the gurus on this would be greatly appreciated!

Thanks,

-Michel

Re: Benchmark of using JSON to transport query results in node.js

[Michel Pelletier]

On Fri, Jan 11, 2019 at 10:31 AM Mitar  wrote:

> Hi!
>
> > Another option is to query directly from node.js and get JSON or native
> query from the database (extensive use of functions / stored procedures).
>
> For web applications, I was even thinking about this crazy approach:
> get PostgreSQL to encode all results in JSON, and then in node.js do
> not parse JSON, but send it as string directly to the browser.


Not crazy at all, that's basically how PostgREST works:


http://postgrest.org/en/v5.2/

-Michel

>