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
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 ex
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,
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
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
>
> 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
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
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
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:
>
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
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
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
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
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 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
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;
> >>
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 refc
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 objectSubI
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
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 tra
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
>
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
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,
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.
>
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
>
> >
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
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
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
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:
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
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
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)
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 implem
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
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
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
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
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
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
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
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
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
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
>
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)
>
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,
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
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
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
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
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
; 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 t
air" 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
>> a
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
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:
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;
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
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.
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
&
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 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
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&g
,
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
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.
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
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
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,
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
67 matches
Mail list logo
