On Fri, Apr 25, 2025 at 3:29 PM Vydehi Ganti wrote:
> Can i know if there is any scenario or ref document for the design you
> suggested above?
Some docs:
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
https://satoricyber.com/postgres-security/postgres-row-level-security/
Your Ora
On Fri, Apr 25, 2025 at 3:21 PM Vydehi Ganti wrote:
> So I don't have a possibility to append where clause dynamically and can only
> check the boolean?
Indeed. But given that you can run arbitrary SQL inside the function,
even dynamic SQL,
that ends up pretty much the same. And you have access
On Fri, Apr 25, 2025 at 3:01 PM Vydehi Ganti wrote:
> Then it should build up the lPredicate with the filter condition and append
> to the query user runs on the Activity table.
You're not reading us, and asking us to do the work for you...
RLS Predicates don't return strings in PostgreSQL, but
On Fri, Apr 25, 2025 at 2:43 PM Laurenz Albe wrote:
> On Fri, 2025-04-25 at 12:38 +0530, Vydehi Ganti wrote:
> > We are presently using Postgresql:PostgreSQL 15.12 on x86_64-pc-linux-gnu,
> > compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-23), 64-bit
> > 2.The function would return a charact
On Fri, Apr 25, 2025 at 9:09 AM Vydehi Ganti wrote:
> We are presently using Postgresql:PostgreSQL 15.12 on x86_64-pc-linux-gnu,
> compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-23), 64-bit
> I have a scenario where
> 1.I need to enforce RLS policy on a table for Select by calling a Function
On Thu, Apr 17, 2025 at 11:13 AM 王 博 wrote:
> Hello PostgreSQL Community,
> We have encountered unexpected SQL parsing errors during application upgrades
> from PostgreSQL 14 to 15 and 16, related to the use of JDBC-style parameter
> placeholders (`?`) in our legacy applications.
AFAIK, Postgre
On Wed, Apr 16, 2025 at 4:39 PM Tom Lane wrote:
> Laurenz Albe writes:
> > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:
So in a way, you guys are saying one should never REVOKE CONNECT ON
DATABASE FROM PUBLIC?
All my DBs are not PUBLIC-accessible.
And inside my DBs,
On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe wrote:
> On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote:
> But then you fortunately cannot drop all databases, because you cannot
> drop the database you are connected to.
>
> Still, a cluster that is missing "postgres" will give beginners troub
Hi. I'm on v16+.
The DB owner ROLE has CREATEROLE, and obviously CREATE on the DB.
So it can both CREATE SCHEMA, and CREATE ROLE.
Yet it cannot CREATE SCHEMA AUTHORIZATION, and gets an
ERROR: must be able to SET ROLE "..."
Yet because this is v16+, thus the DB owner has ADMIN OPTION on the
ROLEs
On Mon, Apr 14, 2025 at 12:09 PM Wolfgang Walther
wrote:
> > From Dominique Devienne:
> > Can't event-triggers also be available to DB owners, instead of just
> > SUPERUSER?
>
> There's a recent -hackers thread exactly about this:
> https
Hi.
I'd like to use CREATE EVENT TRIGGER, but they are SUPERUSER only.
In the past, CREATE EXTENSION was also SUPERUSER-only, but is now also
available to DB owners. Which is great!
Can't event-triggers also be available to DB owbers, instead of just SUPERUSER?
Having CREATEDB does not imply ha
On Fri, Apr 11, 2025 at 5:52 AM Tom Lane wrote:
> Merlin Moncure writes:
> > I guess the real problems here are lack of feedback on a number of fronts:
> > *) the server knows the function is not immutable but lets you create it
> > anyway, even though it can have negative downstream consequences
On Thu, Mar 20, 2025 at 4:43 PM Sebastien Flaesch
wrote:
> OR ( I guess I start to understand the code... ) it this comment only for:
> #define CASHOID MONEYOID
> #define LSNOID PG_LSNOID
That's what Tom already replied, yes. --DD
On Thu, Mar 6, 2025 at 11:24 AM Ron Johnson wrote:
> I already do that. This is part of a long chain of commands so I'm trying to
> minimize the length of commands.
but given that your regexp patterns are not anchored, they are not
equivalent. I think mine is "more correct".
> Anyway, it would
On Thu, Mar 6, 2025 at 10:38 AM Ron Johnson wrote:
> This statement runs great from the psql prompt. Does exactly what I want.
> select datname from pg_database WHERE datname !~ 'template|postgres' ORDER BY
> datname;
>
> But it doesn't work so well from the bash prompt. Not escaping the "!"
>
On Mon, Feb 24, 2025 at 5:39 PM Greg Sabino Mullane
wrote:
> On Mon, Feb 24, 2025 at 4:46 AM Dominique Devienne
> wrote:
>
>> But now we have a new requirement, for "fuzzy find". I.e. the client can
>> ask for names
>> which are not the exact in-DB n
On Mon, Feb 24, 2025 at 3:51 PM Rob Sargent wrote:
> API:
> > * The first type never relies on fuzzy-find.
>
> Then have that app type use the simple, fast query.
>
I'm sorry, but that's neither helpful, nor relevant. --DD
On Mon, Feb 24, 2025 at 12:33 PM Matthias Apitz
wrote:
> Thanks for your hint, Jeremy. But this does not work either:
>
> postgres=# SELECT collname, collversion FROM pg_collation where collname =
> 'de_DE.utf8';
> collname | collversion
> +-
> de_DE.utf8 | 2.38
> (1 r
Hi,
We have a few dozen queries involving grandparent, parent, child table
triplets,
to implement a pre-existing API on top of PostgreSQL. That API is not
SQL-based
nor SQL "friendly" either, that's why we detect patterns in the APIs
inputs, to use
different SQL queries, as (important) optimizatio
On Thu, Feb 20, 2025 at 4:27 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Error: DDL Error: GRANT USAGE ON SCHEMA "SCH1", "SCH2" TO "SCH2:RO",
> > "SCH2:RW", "SCH2:SU": #XX000: ERROR: tuple concurrently updated
>
> S
On Fri, Feb 21, 2025 at 3:45 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Friday, February 21, 2025, Dominique Devienne
> wrote:
>
>> On Fri, Feb 21, 2025 at 3:33 PM Tom Lane wrote:
>>
>>> Dominique Devienne writes:
>>> > The
On Fri, Feb 21, 2025 at 3:33 PM Tom Lane wrote:
> Dominique Devienne writes:
> > The point I'm trying to make, is that "hunting down" grantor(s) to
> connect
> > to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish
> > t
On Thu, Feb 20, 2025 at 5:52 PM Tom Lane wrote:
> So grants and revokes are still being done as the object owner by
> default.
>
> Now I'm unclear on exactly what was happening in Dominique's case.
> Was the problematic permission granted by somebody other than the
> database's owner?
>
Here's m
On Thu, Feb 20, 2025 at 5:05 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thursday, February 20, 2025, Dominique Devienne
> > wrote:
> >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
> >> did nothing, even with
Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently
did nothing, even with CASCADE, when I was running it as SUPERUSER,
preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do
the REVOKE, which DID something this time, and then I could DROP the role.
That's
On Thu, Feb 20, 2025 at 4:27 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. A tester just tried to restore two custom backups (not official
> > PostgreSQL ones) concurrently.
> > ...
> > The second session completed OK.
> > But the first session errors
Hi. A tester just tried to restore two custom backups (not official
PostgreSQL ones) concurrently.
The restore process creates a new schema, some roles,and does some
role-to-role grants, before loading the data.
The two restores are independent, i.e. create different non-overlapping
schemas.
But b
On Thu, Feb 20, 2025 at 3:31 PM Ron Johnson wrote:
> On Thu, Feb 20, 2025 at 8:07 AM Dominique Devienne
> wrote:
>
>> Hi. I've just read
>> https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/
>>
>> and I'm wondering whether t
Hi. I've just read
https://www.dbi-services.com/blog/postgresql-18-virtual-generated-columns/
and I'm wondering whether there will be a way to ALTER existing STORED
generated columns, to be virtual? W/o rewriting the whole table that is.
Thanks, --DD
On Thu, Feb 13, 2025 at 4:09 PM Greg Sabino Mullane
wrote:
> On Wed, Feb 12, 2025 at 9:02 PM Marcelo Fernandes
> wrote:
>
>> What I am after is the same, but I seek a deeper understanding of what it
>> does, and why it does it. For example, it swaps relfilenode. Why?
>
>
> It is surgically repla
On Sun, Jan 19, 2025 at 9:23 AM Alex Burkhart wrote:
> I'm looking for help to organize locks and transaction for a reliable task
> queue.
Have a look at https://github.com/tembo-io/pgmq for inspiration maybe. --DD
On Fri, Jan 10, 2025 at 10:13 AM Vladlen Popolitov
wrote:
> If you really need 1-byte integer, you can use "char" type. Cast it
> to/from int. See comment at the end of the page
> https://www.postgresql.org/docs/17/datatype-character.html
Hi. What would be the best online doc to learn about the p
On Mon, Jan 6, 2025 at 12:21 PM Stijn Sanders wrote:
> I've been using LibPQ to get data from PostgreSQL instances with great
> success.
> I'm using PQsendQuery and PQgetResult, but noticed there's also
> PQsetSingleRowMode.
> The documentation is clearly stating it only benefits a limited set o
On Thu, Dec 19, 2024 at 3:37 PM Daniel Frey wrote:
> I'm adding support for Pipeline Mode to my C++ PostgreSQL client library [...]
> Anyway, is there some documentation about how these modes interact and how
> they can be combined? Or should they never be combined?
Hi. Happy New Year. I was int
On Wed, Nov 27, 2024 at 3:14 PM Raphael Salguero Aragón
wrote:
> Am 23.09.2024 um 17:37 schrieb Dominique Devienne :
> > I'd be glad to use Pavel's proposed %N. —DD
> I just wanted to add that we are also supporting a larger customer with
> exactly the same request.
>
On Wed, Nov 13, 2024 at 11:29 AM Alvaro Herrera wrote:
> On 2024-Nov-13, Vijaykumar Jain wrote:
> > I tried to grant select permissions to 5000 different roles on one table,
> > It failed with row size too big already at 2443.
>
> But you can grant select to one "reader" role, and grant that one r
On Thu, Nov 7, 2024 at 7:04 PM Adrian Klaver wrote:
> On 11/7/24 09:55, Dominique Devienne wrote:
> > On Thu, Nov 7, 2024 at 6:39 PM Daniel Verite
> > wrote:
> >> Dominique Devienne wrote:
> >>> Also, does the code for per-type _send() and _recv()
On Thu, Nov 7, 2024 at 6:39 PM Daniel Verite wrote:
> Dominique Devienne wrote:
> > Also, does the code for per-type _send() and _recv() functions
> > really change across versions of PostgreSQL? How common are
> > instances of such changes across versions?
On Thu, Nov 7, 2024 at 5:37 PM Adrian Klaver wrote:
>
> On 11/6/24 08:20, Dominique Devienne wrote:
> >>From https://www.postgresql.org/docs/current/sql-copy.html:
> > |> binary-format file is less portable across machine architectures
> > and PostgreSQL versions
>From https://www.postgresql.org/docs/current/sql-copy.html:
|> binary-format file is less portable across machine architectures
and PostgreSQL versions
In my experience, the binary encoding of binding/resultset/copy is
endian neutral (network byte order), so what is the less portable
across machi
On Tue, Nov 5, 2024 at 7:25 PM Tom Lane wrote:
> Dominique Devienne writes:
> > On Tue, Nov 5, 2024 at 6:42 PM Tom Lane wrote:
> >> This is probably a bug, at least to the extent that we don't like
> >> XX000 errors to be easily reachable, so please le
On Tue, Nov 5, 2024 at 6:42 PM Tom Lane wrote:
> Dominique Devienne writes:
> > On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:
> > GRANT "...:USER" TO "ddevienne" #XX000: ERROR: no possible grantors
> > Any clue to what this m
Hi. Still working on porting our stuff to v16+, with the ROLE changes.
I'm facing a new issue. The same code and unit test works in v14,
when the LOGIN user running the test is INHERIT or NOINHERIT.
On v16 and v17 though, it works with INHERIT, but with NOINHERIT, I get:
GRANT "...:USER" TO "dde
On Thu, Oct 31, 2024 at 1:04 PM Bruno Wolff III wrote:
> I was unable to find any documentation clarifying if using COPY to load a
> table with rows referencing others rows in the same table, whether I
> had to worry about ordering of the input.
> What I found (in 17) is that even if the reference
On Tue, Oct 22, 2024 at 6:03 PM Achilleas Mantzios
wrote:
> Στις 22/10/24 18:54, ο/η Colin 't Hart έγραψε:
> This works in Postgres 15:
> Do this instead :
> create function json_test(out value text, out jsonparam jsonb)
...
> apparently json is a reserved word (now) and won't be accepted as funct
On Wed, Oct 16, 2024 at 2:25 PM wrote:
> I'd like to be able to use psql without typing passwords again and
> again. I know about `.pgpass` and PGPASSFILE, but I specifically do not
> want to use it - I have the password in the `.env` file, and having it
> in _two_ places comes with its own set o
I have an existing heavy ETL that serially loads tons of data to PostgreSQL.
This is done using a CLI tool, processing one project after another.
I'd like to parallelize / distribute the work, which I could do from my CLI
tool, but 1) that would be confined to a single machine, and 2) we'd like to
On Thu, Oct 10, 2024 at 4:19 PM Erik Wienhold wrote:
> > > On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> > > > On a related but different matter, is it normal not having access to a
> > > > single tablespace makes the whole output disappear?
>
On Thu, Oct 10, 2024 at 3:40 PM Erik Wienhold wrote:
> On 2024-10-10 14:35 +0200, Dominique Devienne wrote:
> > Hi. Why isn't the ::regrole::text cast working as usual?
> > Aren't the OIDs for grantor and grantee returned by acldefault() valid
> > ROLEs?
>
Hi. Why isn't the ::regrole::text cast working as usual?
Aren't the OIDs for grantor and grantee returned by acldefault() valid ROLEs?
C:\Users\ddevienne>psql service=...
psql (17.0)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off, ALPN: postgresql)
Type "help"
On Mon, Sep 23, 2024 at 5:16 PM Adrian Klaver wrote:
> On 9/23/24 08:07, Dominique Devienne wrote:
> > I often resort to \conninfo, but it's less automatic and
> > harder to visually parse (IMHO) compared to a custom ad-hoc prompt.
> For me that shows the user that conn
On Mon, Sep 23, 2024 at 4:55 PM Tom Lane wrote:
> Laurenz Albe writes:
> > To get the current role, psql would have to query the database whenever
> > it displays the prompt. That would be rather expensive...
>
> See previous discussion:
> https://www.postgresql.org/message-id/flat/CAFj8pRBFU-Wz
On Mon, Sep 23, 2024 at 3:05 PM Laurenz Albe wrote:
> On Mon, 2024-09-23 at 14:59 +0200, Dominique Devienne wrote:
> > On Mon, Sep 23, 2024 at 2:51 PM Erik Wienhold wrote:
> > > You could instead use this:
> > >
> > > SELECT current_role \gset
> >
On Mon, Sep 23, 2024 at 2:51 PM Erik Wienhold wrote:
> You could instead use this:
>
> SELECT current_role \gset
> \set PROMPT1 '%n@%/ (%:current_role:)=%# '
>
> But that won't work with subsequent SET ROLE commands.
Bummer... That was kinda the point, that it updates automatically.
Then
On Fri, Sep 20, 2024 at 8:49 PM Robert Haas wrote:
> On Fri, Sep 20, 2024 at 2:34 PM Tom Lane wrote:
> > I'm now inclined to add wording within the pg_has_role entry
> I don't have an opinion about the details, but +1 for documenting it
+1 as well. Especially since I now recall, in hindsight,
ab
On Fri, Sep 20, 2024 at 6:51 PM Robert Haas wrote:
> On Fri, Sep 20, 2024 at 12:37 PM Laurenz Albe
> wrote:
> > That would be a useful addition, yes.
>
> I think this already exists. The full list of modes supported by
> pg_has_role() is listed in convert_role_priv_string(). You can do
> somethi
On Mon, Sep 23, 2024 at 2:22 PM Asad Ali wrote:
> There is no direct prompt escape sequence like %n for displaying the
> current_role in the psql prompt.
> However, you can work around this by using a \set command to define a custom
> prompt that includes the result of current_role.
Hi Ali. Doe
Hi. I've successfully customized my psql PROMPT1,
using %n for session_user, but I'd like to see
current_role as well. And I can't seem to find a way.
I didn't find a direct \x for it.
I didn't find a %'X' variable for it.
I didn't find a command to %`X` either.
(and X = `select current_role` does
To find out whether a ROLE can DROP another in v16+.
Prior to v16, just having CREATEROLE was enough,
so it didn't really seem necessary.
But knowing whether DROP ROLE will work,
w/o invalidating the current transaction,
seems like something quite useful to know now, no?
I can query pg_auth_membe
On Thu, Sep 12, 2024 at 3:53 PM Greg Sabino Mullane wrote:
> On Thu, Sep 12, 2024 at 9:12 AM Dominique Devienne
> wrote:
>> On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane
>> wrote:
>> > (Also note that determining if a database or user exists does not even
On Thu, Sep 12, 2024 at 2:40 PM Dominique Devienne wrote:
> Basically the above explain why we have that
> dd_user (INHERIT)
> `-> member-of dd_admin (NOINHERIT)
> `-> member-of dd_owner (INHERIT).
>
> In pre-v16, once again, this was fine.
> Because v16+ a
On Thu, Sep 12, 2024 at 3:06 PM Greg Sabino Mullane wrote:
> (Also note that determining if a database or user exists does not even
> require a successful login to the cluster.)
Hi. How so? I was not aware of such possibilities.
Can you please give pointers (docs, examples) of this?
Thanks, --D
On Wed, Sep 11, 2024 at 10:20 PM Pavel Luzanov wrote:
> On 11.09.2024 22:21, David G. Johnston wrote:
>> I think this needs to be the other way around:
> [...]. In any case fixing the with admin error is the correct approach.
>
> Unfortunately, it won't work.
> Dominique is right. This will lead
On Wed, Sep 11, 2024 at 11:39 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. I'm going around in circles trying to solve an issue with our
> > system when running against a PostgreSQL v16 server. Which is linked
> > to the weakening of CREATEROLE to have
On Wed, Sep 11, 2024 at 5:09 PM Adrian Klaver wrote:
> What user did you do the above as?
My own user, which lacks SUPERUSER (I have CREATEROLE and CREATEDB
only, and LOGIN of course).
> On my Postgres 16.4 instance logged in as postgres:
> test=# create role dd_owner createrole;
> CREATE ROLE
On Wed, Sep 11, 2024 at 5:06 PM David G. Johnston
wrote:
> As the error indicates, this grant needs to be done with admin option.
Hi David. I did as you suggested, and it fails the same way. Did I
misunderstand you? --DD
D:\pdgm\trunk\psc2>psql service=pau16
psql (17beta3, server 16.1)
Type "hel
Hi. I'm going around in circles trying to solve an issue with our
system when running against a PostgreSQL v16 server. Which is linked
to the weakening of CREATEROLE to have more granular permissions. I've
distilled it down to a simple workflow, as shown below on both v14
(OK) and v16 (KO).
In our
On Sun, Sep 8, 2024 at 8:27 PM Adrian Klaver wrote:
> > simple INSERT ... SELECT was more than twice as fast as 8 parallel COPY
> > operations (and about 8 times as fast as a single COPY).
>
> Yeah they seem to have changed a great deal. Though you are correct in
> saying COPY is not faster then
On Tue, Sep 3, 2024 at 4:31 PM Andrus wrote:
> GRANT "eeva_owner" TO "ingmar.e"
> This command throws error
> ERROR: permission denied to grant role "eeva_owner"
Works are expected when I try it. FWIW. --DD
c:\Users\ddevienne>psql service=pau16
psql (17beta3, server 16.1)
ddevienne=> creat
On Tue, Aug 27, 2024 at 12:23 PM Dominique Devienne wrote:
> On Wed, Aug 14, 2024 at 2:50 PM Dominique Devienne
> wrote:
> > Hi. I've now used successfully the new PQsocketPoll() API
> > in the context of waiting for notifications, using beta2 and 3.
> >
> > B
On Wed, Aug 14, 2024 at 2:50 PM Dominique Devienne wrote:
> Hi. I've now used successfully the new PQsocketPoll() API
> in the context of waiting for notifications, using beta2 and 3.
>
> But now I'm looking into using it in the context of PIPELINE mode.
> Where I
Hi. I've now used successfully the new PQsocketPoll() API
in the context of waiting for notifications, using beta2 and 3.
But now I'm looking into using it in the context of PIPELINE mode.
Where I suppose both forRead and forWrite are 1, but the return
code only indicates whether the condition is
On Tue, Aug 13, 2024 at 10:54 PM Peter J. Holzer wrote:
> You could look at the data files. Tables in PostgreSQL are stored as a
> series of 1GB files, so you watching them being created and/or read
> gives you a pretty good idea about progress.
Thanks Peter, very insightful. Appreciated. --DD
On Tue, Aug 6, 2024 at 4:48 PM Dominique Devienne wrote:
> On Tue, Aug 6, 2024 at 4:31 PM Tom Lane wrote:
> > Dominique Devienne writes:
> > Indeed, that's an oversight, and there's a number of other things
> > we added to libpq-fe.h between 16 and 17 that
On Tue, Aug 6, 2024 at 10:53 AM Dominique Devienne wrote:
> On Mon, Aug 5, 2024 at 2:26 PM David Rowley wrote:
> > Meson is now the only available method for Visual Studio builds."
>
> Thanks David. My colleague figured it out, thanks to your pointers. Cheers,
> -
On Tue, Aug 6, 2024 at 4:31 PM Tom Lane wrote:
> Dominique Devienne writes:
> > Hi. Now that v17beta2 is part of my dev-env, I'm trying out the new API.
> > And I discover there's no version macro for conditional compilation in
> > LIBPQ...
>
> Indeed, that
Hi. Now that v17beta2 is part of my dev-env, I'm trying out the new API.
And I discover there's no version macro for conditional compilation in LIBPQ...
I found https://www.postgresql.org/message-id/968815.1623936849%40sss.pgh.pa.us
where Tom thinks that a horrible idea, and instead proposes a new
On Mon, Aug 5, 2024 at 2:26 PM David Rowley wrote:
> Meson is now the only available method for Visual Studio builds."
Thanks David. My colleague figured it out, thanks to your pointers. Cheers, --DD
On Mon, Aug 5, 2024 at 5:01 PM Tom Lane wrote:
>
> Dominique Devienne writes:
> > The reason I find the restriction damaging is that `FROM t1, t2 WHERE
> > t1.c1 = t2.c2`
> > is the "old" way to write joins, versus the "newer" `FROM t1 JOIN t2
On Mon, Aug 5, 2024 at 3:56 PM Tom Lane wrote:
> Dominique Devienne writes:
> > In https://sqlite.org/forum/forumpost/df23d80682
> > Richard Hipp (Mr SQLite) shows an example of something
> > that used to be supported by SQLite, but then wasn't, to be
> > com
Hi. I'm using as the go-between for a colleague that's not subscribed
to this ML.
In short, we have Python-based automation scripts to build our 3rd parties.
And the ones we have, that worked fine with v16, do not with v17
beta2, on Windows.
Thus I'm asking for advice as how we should proceed. TIA
In https://sqlite.org/forum/forumpost/df23d80682
Richard Hipp (Mr SQLite) shows an example of something
that used to be supported by SQLite, but then wasn't, to be
compatible with PostgreSQL.
Thus I'm curious as to why PostgreSQL refuses the first formulation.
Could anyone provide any insights? Th
On Thu, Jul 25, 2024 at 7:42 AM veem v wrote:
> I was thinking the individual statement will work fine if I pull out those
> from the begin..end block, as those will then be not bounded by any outer
> transaction.
> However, When I was trying it from dbeaver by extracting individual index
> cr
On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
wrote:
> Just know that SQLite does not enforce types [...]
That's true, and applies to the OP's schema.
But for the record, SQLite *can* enforce types these days,
on an opt-in basis, with [STRICT tables][1].
Albeit with a limited type-system. --DD
Hi. So for business rule reason,
INSERT'ing a new row is wrapped in a DEFINER function,
that returns the newly inserted row's OK (and integral ID).
And in the code calling that function, I was
SELECT * FROM table WHERE ID = insert_row_via_func()
to "save a round trip", combining the inse
On Wed, Jun 26, 2024 at 2:42 PM David G. Johnston
wrote:
> On Wednesday, June 26, 2024, Dominique Devienne wrote:
>> Only session_user
>> is representative of the caller, and reliable (modulo SUPERUSER and
>> SET AUTHORIZATION, but that's a different story and kinda n
On Wed, Jun 26, 2024 at 12:11 PM wrote:
> Dominique Devienne:
> > 1) Is there any way to know the current_role of the caller of a
> > DEFINER function. I fear the answer is no, but better be sure from
> > experts here.
>
> You can do something like this:
>
> C
Hi. I was led to believe (by an hallucination...) that I could know
the current_role of the caller of a DEFINER function, but after actual
experimentation, turns out it shows the OWNER of the function, and not
the current_role of the caller.
I foolishly thought curent_role != current_user inside t
Hi. Two things related to the search_path.
First, [the doc][1] mentions one can use a variable like "$user" for
the search_path. But setting the search_path is also for FUNCTIONs and
PROCEDUREs, and there what I really REALLY would like, is the ability
to use "$owner", to limit the search_path to
On Tue, Jun 11, 2024 at 5:29 PM David G. Johnston
wrote:
> On Tuesday, June 11, 2024, Dominique Devienne wrote:
>> Are Stored PROCs running in a different backend?
>> Are Triggers running in a different backend?
> No to both. Whatever backend the SQL to invoke those
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
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'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
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
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 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 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 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 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 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.
>&
1 - 100 of 315 matches
Mail list logo
