On Thu, Apr 27, 2023 at 11:24 AM Laurenz Albe
wrote:
> On Thu, 2023-04-27 at 14:48 +0530, Tushar Takate wrote:
> > Does PostgreSQL support in-transit compression for a client connection?
>
> No, not any more.
>
On a related but different subject, as someone who must store ZLIB (from
ZIP files)
a
On Fri, Apr 28, 2023 at 9:03 AM Magnus Hagander wrote:
> On Thu, Apr 27, 2023 at 11:55 AM Laurenz Albe
> wrote:
> >
> > On Thu, 2023-04-27 at 11:44 +0200, Dominique Devienne wrote:
> > > as someone who must store ZLIB (from ZIP files)
> > > and sometimes LZ
Hi. With an integer identity primary key table,
we fetch a number of rows with WHERE id = ANY($1),
with $1 an int[] array. The API using that query must return
rows in the input int[] array order, and uses a client-side
mapping to achieve that currently.
Is it possible to maintain $1's order direc
On Tue, May 9, 2023 at 11:23 AM David Wheeler wrote:
> > Hi. With an integer identity primary key table,
> > we fetch a number of rows with WHERE id = ANY($1),
> > with $1 an int[] array. The API using that query must return
> > rows in the input int[] array order, and uses a client-side
> > mapp
On Wed, May 10, 2023 at 9:49 AM Andrew Gierth
wrote:
> Dominique> Is it possible to maintain $1's order directly in SQL?
>
> This is the correct way:
>
> SELECT ... FROM unnest($1) WITH ORDINALITY AS u(id,ord)
>JOIN yourtable t ON t.id=u.id
> ORDER BY u.ord;
>
Thanks Andrew, for sp
On Wed, May 10, 2023 at 1:08 PM Andrew Gierth
wrote:
> >>>>> "Dominique" == Dominique Devienne writes:
> Dominique> I assume that if the PK is composite, and I pass the PK
> Dominique> tuples as separate same-cardinality "parallel" arrays,
On Thu, May 18, 2023 at 2:28 PM Victor Nordam Suadicani <
v.n.suadic...@gmail.com> wrote:
> Is there any nice way to handle sum types (aka tagged unions) in a
> PostgreSQL database? [...]
>
A third method would be to save all fields of all variants into a single
> table, with all fields being null
On Sat, May 20, 2023 at 4:43 PM Marcos Pegoraro wrote:
> I have a table like pg_settings, so records have name and value.
>
Hi. Maybe I'm missing something, but why aren't you simply doing:
select name, varvalue from sys_var where name = any($1)
and binding your 4 (in your examples) or 10
Hi. We emulated a legacy security model (enforced in C/C++ code)
into "layers" of PostgreSQL ROLEs and GRANTs, thus enforced database-side.
To troubleshoot and validate that emulation, I'd like to introspect ROLE
membership to:
1) Output the ROLE "path(s)" between any two ROLEs. Typically between
On Tue, Jun 13, 2023 at 2:20 PM Joe Conway wrote:
> On 6/13/23 04:17, Dominique Devienne wrote:
> > To troubleshoot and validate that emulation, I'd like to introspect ROLE
> > membership to:
> >
> > 1) Output the ROLE "path(s)" between any two ROLEs.[.
On Fri, Jun 16, 2023 at 2:50 PM Laurenz Albe
wrote:
> There is an existing solution for that: the libpq connection service file:
> https://www.postgresql.org/docs/current/libpq-pgservice.html
The problem with the service and password files is that passwords are in
plain text though.
Yes there a
On Mon, Jun 19, 2023 at 1:01 PM Thomas Markus
wrote:
> Am 19.06.23 um 12:33 schrieb Peter J. Holzer:
> > As Francisco already pointed out, this can't work with nginx either.
> anyway, fw or nginx cant look into tcp streams
>
Traefik can proxy PostgreSQL, I've read, so maybe that's something you
On Tue, Jun 20, 2023 at 10:47 PM Lorusso Domenico
wrote:
> Could work, but is there a way to set a reference key over the uuid of all
> the tables?
>
Yes, it's possible. We do it. There are several ways to emulate what I call
"polymorphic" FKs.
All approaches have pros and cons, the one we use
For example with [NOTIFY][1]. The doc states:
> Payload: This must be specified as a simple string literal
Does that mean we cannot bind the payload?
I.e. the pseudo code:
```
conn.exec(bind(msg), "NOTIFY {} $1", conn.escapeName(channel));
```
is invalid? And I must use instead
```
conn.exec("NO
On Wed, Jun 21, 2023 at 1:52 PM Laurenz Albe
wrote:
> On Wed, 2023-06-21 at 11:00 +0200, Dominique Devienne wrote:
> > [...] obvious way to know what can and cannot be bound, just from the
> doc?
>
> This is not adequately documented.
Bummer.
> The documentation for
On Wed, Jun 21, 2023 at 4:20 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> The planner is the thing that handles binds. [...]
>
Depends what you mean by "handles", since when I asked about "bind peeking"
during planning,
I think the answer was that it was not supported. So I don't
Hi,
I've looked in the Catalogs, the doc for LISTEN, NOTIFY, even [LibPQ Async
Notif][1],
and I don't see anything about that.
Can I introspect which "channel(s)" the current (or any other session) is
LISTEN'ing to?
Any way to see pending notification(s) in that 8GB queue, from client apps?
pg_
On Thu, Jun 22, 2023 at 3:30 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Can I introspect which "channel(s)" the current (or any other session) is
> > LISTEN'ing to?
>
> The pg_listening_channels() function will show you channel names the
> curre
Hi,
To measure throughput, I'm timing exec time spent in LibPQ,
against the size of the result-set in bytes, as reported
by PQresultMemorySize().
*EXEC: 7x ( 130,867 rows, 54,921,532 bytes) in 0.305s
(171.8 MB/s)*
*EXEC: 8x ( 180,079 rows, 95,876,047 bytes) in 0.49
On Wed, Jun 28, 2023 at 1:22 PM Tom Lane wrote:
> I wrote:
> > That number is the total space actually requested from malloc() for
> > the PGresult object. But we request space in blocks (typically 2KB
> > each), so there's some overhead due to fields not exactly filling
> > a block, unused spac
Assuming a particular schema is standalone, i.e. does not depend
on any other external schema, I'd like to know the order in which to
(re)populate tables from data coming from (an existing) custom storage.
When the schema's table have foreign key relationships,
"parent" tables must be populated be
On Fri, Jul 21, 2023 at 4:58 PM Tom Lane wrote:
> Joseph Koshakow writes:
> > Is this an intentional decision to not support a binary encoding for
> > aclitem types? Or is it just a lack of a feature?
>
I'm also using binary input/output, and for ACLs, when not using the usual
ACL related funct
On Wed, Jul 26, 2023 at 11:26 AM Dionisis Kontominas
wrote:
> On Wed, 26 Jul 2023 at 11:18, Thomas Kellerer wrote:
>
>> Dionisis Kontominas schrieb am 26.07.2023 um 11:00:
>> > do not want two records to overlap, for the same user, the same role
>> > and also when the f_is_deleted is TRUE only.
On Wed, Aug 23, 2023 at 7:46 PM Tom Lane wrote:
> Hellen Jiang writes:
> > I have created readonly and readwrite roles with the following grants:
> however, readonly user does not have access to the new tables created by
> readwrite user. (readonly user has the access to new tables created by
>
On Thu, Aug 24, 2023 at 11:34 AM Erik Wienhold wrote:
> > On 24/08/2023 10:11 CEST Dominique Devienne wrote:
> > On Wed, Aug 23, 2023 at 7:46 PM Tom Lane wrote:
> > > The readwrite user (not the admin) would need to issue ALTER DEFAULT
> > > PRIVILEGES gran
On Thu, Aug 24, 2023 at 10:07 PM Stephen Frost wrote:
> Greetings,
>
> * Emile Amewoto (emil...@yahoo.com) wrote:
> > Here is the high level process:
> > 1- Create the user x without password in Postgres.
> > 2- Assign role or roles to the user x
> > 3- Update pg_hba.conf with the ldap connecti
On Fri, Aug 25, 2023 at 3:09 PM Stephen Frost wrote:
Hi Stephen. Thanks for taking the time to make such a detailed response.
* Dominique Devienne (ddevie...@gmail.com) wrote:
> > could you please provide more info on [...]
>
>
> https://www.crunchydata.com/blog/windows-a
On Mon, Sep 4, 2023 at 3:47 PM Erik Wienhold wrote:
> On 04/09/2023 11:51 CEST Lorusso Domenico wrote:
> Transaction control is not possible in functions. Only in procedures
> (CALL)
> and DO blocks.
>
> > ERROR: syntax error at or near "to"
> > LINE 41: rollback to savepoint deleteAttribute;
>
On Mon, Sep 4, 2023 at 4:22 PM Dominique Devienne
wrote:
> On Mon, Sep 4, 2023 at 3:47 PM Erik Wienhold wrote:
>
>> On 04/09/2023 11:51 CEST Lorusso Domenico wrote:
>> Transaction control is not possible in functions. Only in procedures
>> (CALL)
>> and DO block
On Thu, Sep 7, 2023 at 10:22 PM Tom Lane wrote:
> Erik Wienhold writes:
> > Looks like "Huge input lookup" as reported in [1] (also from Sai) and
> that
> > error is from libxml.
>
> Ah, thanks for the pointer. It looks like for the DOCUMENT case,
> we could maybe relax this restriction by pass
On Fri, Sep 8, 2023 at 11:39 AM Dominique Devienne
wrote:
> On Thu, Sep 7, 2023 at 10:22 PM Tom Lane wrote:
>
>> Erik Wienhold writes:
>> > Looks like "Huge input lookup" as reported in [1] (also from Sai) and
>> that
>> > error is from libxml.
&g
Hi. To administer our PostgreSQL-based system, we have custom tools
to manage the schemas, load data, etc... Including a versatile CLI tool.
But that tool is special purpose, while sometimes we want/need the general
purpose PSQL. But because our ROLE and SCHEMA names are rather long
and unwieldly,
On Fri, Sep 22, 2023 at 12:45 PM Luca Ferrari wrote:
> On Fri, Sep 22, 2023 at 12:13 PM Dominique Devienne
> wrote:
> > So my question is how I pass the password my tool already own, to the
> forked PSQL,
> > w/o that password leaking. I could pass it on the command-line
On Fri, Sep 22, 2023 at 10:56 AM Luca Ferrari wrote:
> On Thu, Sep 21, 2023 at 7:46 PM Dominique Devienne
> wrote:
> > and I also need to run some PSQL \commands and SQL to config PSQL
> correctly
> > for the context our tool was run with (i.e. our tool's own CLI optio
On Fri, Sep 22, 2023 at 5:19 PM Luca Ferrari wrote:
> On Fri, Sep 22, 2023 at 3:24 PM Dominique Devienne
> wrote:
> You added information I was not aware before: you are dumping
> PostgreSQL to restore it into SQLite, while I was thinking you wanted
> to do some stuff with
On Fri, Sep 22, 2023 at 8:56 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > Once you have the password you should utilize the PGPASSWORD environment
> > variable to get it passed to psql. It doesn’t matter in the least how
> you
> > obtained that password in the first place.
>
> Keep in m
Hi. If I do either or both of the changes below:
ddevienne=> alter table dd alter column val set compression lz4;
ALTER TABLE
ddevienne=> alter table dd alter column val set storage extended;
ALTER TABLE
According to the doc, the table is NOT changed.
In my case, I DO want to have the bytea colum
On Tue, Oct 3, 2023 at 6:45 AM Michael Paquier wrote:
> On Tue, Oct 03, 2023 at 06:31:27AM +0200, Laurenz Albe wrote:
> > On Tue, 2023-10-03 at 12:33 +1100, rob stone wrote:
> >> Would running CLUSTER on the table use the new parameters for the re-
> >> write?
> >
> > No, as far as I know.
>
> No
On Tue, Oct 3, 2023 at 9:17 AM Laurenz Albe
wrote:
> On Tue, 2023-10-03 at 09:08 +0200, Dominique Devienne wrote:
> > In my case, it's OK not to be transactional, for these experiments. Is
> there a way
> > to lock the table and do the rewriting w/o generating any
On Thu, Oct 5, 2023 at 11:35 AM Marian Wendt wrote:
> With an INNER JOIN, both tables must be fully checked/matched (check using
> EXPLAIN ANALYSIS -> HashJoin), so the index cannot be used here.
> Sorry, didn't consider the WITH part. Please share the detailed query
> plan for more info.
>
> The
On Fri, Oct 6, 2023 at 4:59 PM Rob Sargent wrote:
> What would be copied? The formula?
Of course not. That's DDL, not DML IMHO.
> Seems to me one is using “the fast option” so adding the column which can
> be regenerated is overhead.
>
Regenerated by whom? COPY TO to is output to the outsi
On Thu, Oct 12, 2023 at 3:42 PM Jana Mihalidesová
wrote:
> I try to find out some view, select or something what show me the
> privileges for the user across the whole postgresql cluster. The
> username/user is global for whole cluster not individual database, so I
> would like to know the privil
Hi. I'm tracing my client-side connection(s) for the first time.
The format seems to be line oriented.
Selected text columns with embedded newlines have those newlines
encoded as \x0a to remain single-line in the trace. So far so good.
But then on Parse, the query text, which also contains embedd
On Tue, Oct 17, 2023 at 6:12 PM Alvaro Herrera
wrote:
> On 2023-Oct-17, Dominique Devienne wrote:
>
> > But then on Parse, the query text, which also contains embedded
> > newlines but also embedded double-quotes, for column aliases, or table
> > names, or whatever, ev
On Sat, Nov 25, 2023 at 5:53 PM hector vass wrote:
> Not sure you need to use array why not simple table joins, so a table with
> your criteria x y z t joined to stuff to give you candidates that do match,
> then left join with coalesce to add the 'd'
>
> select
>
> --a.id,b.test_id,
>
> coalesce
On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver
wrote:
> On 11/25/23 11:21, Davin Shearer wrote:
> > Hello!
> >
> > I'm trying to emit a JSON aggregation of JSON rows to a file using COPY
> > TO, but I'm running into problems with COPY TO double quoting the
> > output. Here is a minimal example
On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne
wrote:
> On Sat, Nov 25, 2023 at 10:00 PM Adrian Klaver
> wrote:
>
>> On 11/25/23 11:21, Davin Shearer wrote:
>> > Hello!
>> >
>> > I'm trying to emit a JSON aggregation of JSON rows to a file us
On Mon, Nov 27, 2023 at 3:56 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > I agree there should be a copy option for “not formatted” so if you dump
> a
> > single column result in that format you get the raw unescaped contents of
> > the column.
>
> I'm not sure I even buy that. JSON dat
On Mon, Nov 27, 2023 at 5:04 PM Adrian Klaver
wrote:
> On 11/27/23 01:44, Dominique Devienne wrote:
> > On Mon, Nov 27, 2023 at 10:33 AM Dominique Devienne > <mailto:ddevie...@gmail.com>> wrote:
> > On second thought, I guess that's COPY in its text modes doing
Our PostgreSQL-based system uses a small number of fixed schemas,
and an arbitrary number of per-project schemas (one schema per project).
This will is given, sorry, cannot be changed. Really.
The fixed schemas contain metadata about the projects (including which
schema a given project uses).
My
Hi. I've got a nice little POC using PostgreSQL to implement a REST API
server.
This uses json_agg(t) to generate the JSON of tables (or subqueries in
general),
which means I always get back a single row (and column, before I added the
count(t.*)).
But I'd like to get statistics on the number of r
On Sun, Dec 10, 2023 at 5:56 PM Ron Johnson wrote:
> * We departitioned because SELECT statements were *slow*. All partitions
> were scanned, even when the partition key was specified in the WHERE clause.
>
Surely that's no the case on newer PostgreSQL, is it? Otherwise what's the
point of part
Hi. We've recently upgraded from libpq 15.2 to 16.1.
We custom build postgresql using the instructions and GCC 9.1 (from RH7's
dts9).
We used the same process for building 15.2 and 16.1.
But somehow psql crashes on any backslash command, while 15.2 works fine.
I've included the small backtrace belo
On Tue, Dec 19, 2023 at 2:02 PM Thomas Munro wrote:
> On Wed, Dec 20, 2023 at 1:39 AM Dominique Devienne
> wrote:
> > Program received signal SIGSEGV, Segmentation fault.
> > 0x004232b8 in slash_yylex ()
>
> I think this might have to do with flex changing. Doe
On Tue, Dec 19, 2023 at 7:58 PM Thomas Munro wrote:
> On Wed, Dec 20, 2023 at 4:41 AM Dominique Devienne
> wrote:
> > On Tue, Dec 19, 2023 at 2:02 PM Thomas Munro
> wrote:
> >> On Wed, Dec 20, 2023 at 1:39 AM Dominique Devienne
> wrote:
> >> > Progra
Hi. Another team (than mine) has been trying to troubleshoot hang issues in
their Node/JS/TS-based mid-tier services,
after a switch from MySQL to PostgreSQL. They run PostgreSQL in a Linux
container (see PID [1] below), unlike us.
They started looking at the PostgreSQL server-side logs, but with l
Hi. And happy new year (for those using the Gregorian calendar).
pg_has_role() from
https://www.postgresql.org/docs/current/functions-info.html
added the 'SET' privilege in v16, and on top of the existing 'MEMBER' and
'USAGE' ones:
> MEMBER denotes direct or indirect membership in the role [...]
On Tue, Jan 2, 2024 at 5:09 PM Adrian Klaver
wrote:
> On 1/2/24 07:24, Dominique Devienne wrote:
> > pg_has_role() from
> > https://www.postgresql.org/docs/current/functions-info.html
> > <https://www.postgresql.org/docs/current/functions-info.html>
> > added t
On Tue, Jan 2, 2024 at 5:11 PM David G. Johnston
wrote:
> On Tue, Jan 2, 2024 at 8:25 AM Dominique Devienne
> wrote:
>
>> pg_has_role() from
>> https://www.postgresql.org/docs/current/functions-info.html
>> added the 'SET' privilege in v16, and on top o
On Fri, Jan 12, 2024 at 6:13 PM David Hussey wrote:
> It would be good to have proper support for Windows arm64 architecture.
>
Hi. Have you seen:
https://www.linaro.org/windows-on-arm/postgres/ ?
https://linaro.atlassian.net/wiki/spaces/WOAR/pages/28707291320/PostgreSQL ?
I just did a quick se
On Mon, Jan 15, 2024 at 5:17 AM veem v wrote:
> Is any key design/architectural changes should the app development team
> [...], should really aware about
>
Hi. One of the biggest pitfall of PostgreSQL, from the app-dev perspective,
is the fact any failed statement fails the whole transaction, w
On Tue, Jan 16, 2024 at 5:07 PM Adrian Klaver
wrote:
> On 1/16/24 00:06, Dominique Devienne wrote:
> > On Mon, Jan 15, 2024 at 5:17 AM veem v > <mailto:veema0...@gmail.com>> wrote:
> > Is any key design/architectural changes should the app development
> >
On Tue, Jan 16, 2024 at 6:10 PM Adrian Klaver
wrote:
> On 1/16/24 09:04, Dominique Devienne wrote:
> > On Tue, Jan 16, 2024 at 5:07 PM Adrian Klaver > <mailto:adrian.kla...@aklaver.com>> wrote:
> >
> > On 1/16/24 00:06, Dominique Devienne wrote:
> >
On Tue, Jan 16, 2024 at 10:59 PM Tom Lane wrote:
> Dominique Devienne writes:
> > But sure, I take your point, you can emulate statement-level (implicit)
> > rollback via an explicit SAVEPOINT, and ROLLBACK to the savepoint
> instead.
>
> > But my point remains, th
>
> Le lun. 22 janv. 2024 à 08:29, James Pang a
> écrit :
>
>> [...] we used to turn on Oracle session trace(that will capture all
>> activities,
>>
> SQL statements, waiting, waiting time), to do living troubleshooting.
>>
> could you direct any similar tracing in Postgresql v13 , v14.
>>
>
On Tu
On Mon, Feb 12, 2024 at 7:50 AM veem v wrote:
> I think that's not much of a concern with PostgreSQL because you can't
>> update a row in-place anyway because of MVCC.
>
>
> Good to know. So it means here in postgres, there is no such concern like
> "row chaining", "row migration" etc.
>
which w
Just an FYI. Running the same test suite against V16 triggered two failures.
First, a test of getting the direct members of a role failed.
Returned one more role. The role that created the introspected role.
That's the new CREATEROLE semantic, which adds the role creator
as a direct member of the
Hi. Our "app" depends on many ROLEs and SCHEMAs, and manages GRANTs between
those.
Typically, each "instance" of our app lives in its own DB, and uses a
naming convention for its ROLEs, to make those role names unique
per-app-instance. All the app roles are created by a single master role
(the "own
On Sat, Feb 17, 2024 at 10:50 PM Lok P wrote:
> We were having past experience in Oracle and are newly getting moved to
> postgres database. [...]
> So I just wanted to understand if these grants and privileges for
> objects/users are given and maintained in a similar way in postgres database
>
On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe
wrote:
> 1. Schemas and users are not tied together, they are orthoginal concepts.
> Just like operating
>system users and directories (and indeed all other databases).
>
Forgot about that one! OTOH, you could say PostgreSQL has tied USERs and
RO
On Sun, Feb 18, 2024 at 3:27 PM Pavel Luzanov
wrote:
> On 18.02.2024 15:19, Dominique Devienne wrote:
>
> On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe
> wrote:
>
>
>> 2. In PostgreSQL, there is the important concept of ownership, which is
>> not tied to the schem
On Sun, Feb 18, 2024 at 4:33 PM Pavel Luzanov
wrote:
> On 18.02.2024 17:40, Dominique Devienne wrote:
>
> Well, membership in a role mean you can "become that role", no? Thus this
> seems logical,
> and not confusing to me, that you can act as the owner, since yo
In the past, I've read [this post][1] from Marc Millas that reports
`pg_dump ... | psql` at throughput around 500MB/s (5Gb/s) on a 10Gb/s
network.
Today, I've tried pg_dump on a single schema of 25 tables, 88K rows, 70MB
total (as reported by pg total relation sizes). 1 table of 225 rows
contains
On Thu, Feb 22, 2024 at 11:41 AM Francisco Olarte
wrote:
> Dominique:
> > in the 10-12MB/s throughput range.
>
> This has the faint smell of a saturated 100Mbps link in the middle
> (12*8=96Mbps)
>
Very good call Francisco! Thanks a lot.
Below is connecting to the v14 PostgreSQL host,
On Thu, Feb 22, 2024 at 12:20 PM Dominique Devienne
wrote:
> On Thu, Feb 22, 2024 at 11:41 AM Francisco Olarte
> wrote:
>
>> Dominique:
>> > in the 10-12MB/s throughput range.
>>
>> This has the faint smell of a saturated 100Mbps link in the midd
Hi. The doc states:
>From https://www.postgresql.org/docs/current/libpq-copy.html:
> Data is always returned one data row at a time;
> if only a partial row is available, it is not returned
I'm doing a COPY TO STDOUT WITH (FORMAT BINARY).
The record format allows up to int16_t columns. (16K or 32
Hi.
For the first time, I'm checking the OIDs returned
(via the result set's PQftype API), by "regular" SELECT:
`select ... from tab`
and by a COPY TO BINARY:
`COPY (
select ...
from tab
) TO STDOUT WITH (FORMAT BINARY)`
And to my surprise, they are not!
The ones from the COPY are all zero
On Tue, Feb 27, 2024 at 6:11 PM Tom Lane wrote:
> Dominique Devienne writes:
> > For the first time, I'm checking the OIDs returned
> > (via the result set's PQftype API), by "regular" SELECT:
> > ...
> > And to my surprise, they are not!
> >
Hi. I just tried adding the xmin pseudo-column to my COPY,
and I get an error. Using a regular SELECT works fine.
Is that normal? that's a `COPY tab(xmin, cols...) ...`
Do I need to switch to a `COPY (select xmin, cols... from tab) ...` to
access xmin?
And if I do that, any performance implicati
On Tue, Feb 27, 2024 at 7:26 PM Alvaro Herrera
wrote:
> On 2024-Feb-27, Dominique Devienne wrote:
>
> > Hi. I just tried adding the xmin pseudo-column to my COPY,
> > and I get an error.
>
> I suggest you find some other way to achieve whatever it is you think
> you&
Hi.
>From https://www.postgresql.org/docs/16/ddl-generated-columns.html:
> PostgreSQL currently implements only stored generated columns
We use generated columns extensively.
And we have foreign-keys attached to those generated columns.
The fact they are always Stored thus wastes space in our cas
On Wed, Feb 28, 2024 at 5:59 PM Laurenz Albe
wrote:
> On Wed, 2024-02-28 at 16:09 +0100, Dominique Devienne wrote:
> > From https://www.postgresql.org/docs/16/ddl-generated-columns.html:
> > > PostgreSQL currently implements only stored generated columns
> >
>
On Wed, Feb 28, 2024 at 8:11 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Views can have foreign-keys?
>
> Surely you'd put the FK on the underlying table.
>
Again, the FKs are on the *generated* columns. So
> > Generated view columns be indexed?
>
&
On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe
wrote:
> On Thu, 2024-02-29 at 08:55 +0100, Dominique Devienne wrote:
> Polymorphic Foreign Keys are nigh impossible to model well in SQL,
> and I doubt that non-stored generated columns will solve that.
>
It is modelled. It works.
On Thu, Feb 29, 2024 at 11:26 AM Peter Eisentraut
wrote:
> On 28.02.24 16:09, Dominique Devienne wrote:
> > Any chance PostgreSQL might gain actual virtual / non-stored generated
> > columns soon? Ever?
>
> I plan to work on this for PG18.
>
Thanks for the update, Peter and Alvaro. --DD
On Thu, Feb 29, 2024 at 11:58 AM Laurenz Albe
wrote:
> On Thu, 2024-02-29 at 10:55 +0100, Dominique Devienne wrote:
> > On Thu, Feb 29, 2024 at 10:03 AM Laurenz Albe
> wrote:
> > > You could use conditional indexes, but then you have to make sure that
> > > the op
Hi,
Our legacy 3-tier backend is adding a feature requested by users,
to keep track of the last-access-time of projects. The primary purpose
is to archive projects which haven't been used (read from) in a while
(offline),
or perhaps move them to a cheaper / slower storage tier (still online, but
s
On Tue, Mar 12, 2024 at 3:30 PM Christophe Pettus wrote:
> > On Mar 12, 2024, at 07:15, Dominique Devienne
> wrote:
> > So is it possible to track the last time a SELECT was performed on some
> TABLE?
>
> Directly, no. You could periodically sample the various table
On Thu, Mar 21, 2024 at 8:10 AM alex work wrote:
> We encounter slow `GRANT ROLES` only on PostgreSQL 16 instances up to 42
> seconds
> in production, the client process at PostgresSQL would use 100% of the
> CPU. [...]
>
Using ROLE `acc`, grant `d_` ROLE to a session ROLE:
> real0m7.579s [..
On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson wrote:
> > However I noticed that comments on roles are also omitted from the dump,
> as if --no--comments flag was set - but it wasn't.
>
> Comments on roles are stored against the pg_authid catalog relation
Hi. What do you mean? ROLEs are not
On Thu, Mar 21, 2024 at 11:52 AM Dominique Devienne
wrote:
> On Thu, Mar 21, 2024 at 11:46 AM Daniel Gustafsson
> wrote:
>
>> > However I noticed that comments on roles are also omitted from the
>> dump, as if --no--comments flag was set - but it wasn't.
>&
On Fri, Mar 22, 2024 at 12:58 PM ushi wrote:
> i am playing with the idea to implement a job queuing system using
> PostgreSQL.
FYI, two bookmarks I have on this subject, that I plan to revisit
eventually:
* https://news.ycombinator.com/item?id=20020501
*
https://www.enterprisedb.com/blog/liste
On Sat, Mar 23, 2024 at 3:13 AM Merlin Moncure wrote:
> On Fri, Mar 22, 2024 at 6:58 AM ushi wrote:
>
>> the idea to implement a job queuing system using PostgreSQL.
>>
>
> I wrote an enterprise scheduler, called pgtask, which ochestates a very
> large amount of work [...]
>
Hi. Anything you ca
On Mon, Apr 8, 2024 at 5:31 PM Sebastien Flaesch
wrote:
> I understand when this can happen, but in fact I do de-allocate prepared
> statements when I should.
>
We've run into similar issues. We're in C++, and with RAII deallocate
resources (Prepared Statements, Cursors) in Dtors.
But as you saw
On Wed, Apr 10, 2024 at 12:11 PM John Bateson
wrote:
> *Using Postgres and PGAdmin -4*.
>
> [...]. This is getting towards 20 tables so far.
>
> [...] i.e. the traditional .EXE file I can put on a button on the screen
> on a Windows 11 based system.
>
John,
Are you aware of SQLite or DuckDB?
Un
On Sun, Apr 21, 2024 at 11:12 PM Tom Lane wrote:
> Steve Baldwin writes:
> > If the number of tenant schemas is reasonably static, you could write a
> > plpgsql function to create a set of UNION ALL views
>
> Another idea is to build a partitioned table
>
Hi Norbert. I asked a [similar question
On Tue, Apr 23, 2024 at 11:08 AM Norbert Sándor
wrote:
> *> And if/when I get back to this issue myself, I'll do the same.*
> My current solution is not much different than the one I posted in my
> original question.
>
> CREATE OR REPLACE FUNCTION tenant_query_json(tbl anyelement)
>
> RETURNS set
Hi. I've noticed [that libpq API in v17 beta1][1], and wanted to use
it to replace an existing Boost.ASIO-based async polling of the
connection's socket, waiting for notifications. The use case being
using PostgreSQL LISTEN/NOTIFY for a simple message queue. The code
needs to be cross-platform Wind
need for fancy types imho. Aren’t betas precisely for the
purpose of exposing apis to those like myself to vet them? This is also
beta1, I,e, the first one. My €0.02
On Mon, Jun 10, 2024 at 6:18 PM Tom Lane wrote:
> Dominique Devienne writes:
> > PQsocketPoll() being based on time_t, it
Hi. I have a unit test using a single connection, that simulates a
client interacting with a server via a PostgreSQL "queue", i.e. a
non-writable table with SECURITY DEFINER procedures to mediate writes
to that table, with those PROC-initiated updates triggering
pg_notify() messages (via an UPDATE
101 - 200 of 343 matches
Mail list logo
