Re: Dynamically accessing columns from a row type in a trigger
On Sun, Aug 13, 2023 at 11:27 AM Rhys A.D. Stewart
wrote:
>
> Hey Adrian,
>
> Thanks for your response. I don't think I explained myself clearly.
> pk_col is not the column name. pk_col is a variable that holds the
> name of a column. This is one trigger for several tables with varying
> structures. So pk_col would be a column specific to the current
> TG_TABLE_NAME when the trigger is invoked. This is why in my example I
> had to use EXECUTE to get the value of the pk_col from OLD.
>
> Actually, now that I'm thinking about it, I don't really want to store
> the value into a variable because the pk_col might be of any given
> type. So ideally, I'd love a way to just get the value from OLD and
> use it directly in another query. Something along the lines of:
>
> `EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
> USING OLD['pk_col']`.
>
> I reckon I may have to look at just generating a trigger function per
> table, or maybe look into using TG_ARGS.
>
google lead me to this post:
https://stackoverflow.com/questions/55245353/access-dynamic-column-name-of-row-type-in-trigger-function
> table, or maybe look into using TG_ARGS.
maybe you are referring to TG_ARGV.
example of TG_ARGV =>
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/triggers.out
Re: Dynamically accessing columns from a row type in a trigger
On Sat, Aug 12, 2023 at 1:10 PM Rhys A.D. Stewart wrote: > Am I missing out on a simpler or more elegant solution? > > No, you are not (at least among SQL and pl/pgsql. SQL is strongly and statically typed. Circumventing that has a cost, but at least you do have tools at hand when you find the need. David J.
Re: Dynamically accessing columns from a row type in a trigger
On 8/12/23 20:21, Rhys A.D. Stewart wrote:
Hey Adrian,
Thanks for your response. I don't think I explained myself clearly.
pk_col is not the column name. pk_col is a variable that holds the
name of a column. This is one trigger for several tables with varying
structures. So pk_col would be a column specific to the current
TG_TABLE_NAME when the trigger is invoked. This is why in my example I
had to use EXECUTE to get the value of the pk_col from OLD.
So you are looking for a generic solution.
Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:
The issue then is determining what value to get from OLD.
Is that generic e.g always the Primary Key or will it vary?
`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.
I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.
Rhys
Peace & Love | Live Long & Prosper
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Dynamically accessing columns from a row type in a trigger
Hey Adrian,
Thanks for your response. I don't think I explained myself clearly.
pk_col is not the column name. pk_col is a variable that holds the
name of a column. This is one trigger for several tables with varying
structures. So pk_col would be a column specific to the current
TG_TABLE_NAME when the trigger is invoked. This is why in my example I
had to use EXECUTE to get the value of the pk_col from OLD.
Actually, now that I'm thinking about it, I don't really want to store
the value into a variable because the pk_col might be of any given
type. So ideally, I'd love a way to just get the value from OLD and
use it directly in another query. Something along the lines of:
`EXECUTE format('SELECT * FROM %1$I.sometable WHERE pk = $1', myschma)
USING OLD['pk_col']`.
I reckon I may have to look at just generating a trigger function per
table, or maybe look into using TG_ARGS.
Rhys
Peace & Love | Live Long & Prosper
On Sat, Aug 12, 2023 at 3:31 PM Adrian Klaver wrote:
>
> On 8/12/23 13:09, Rhys A.D. Stewart wrote:
> > Greetings all,
> >
> > I am writing a trigger and would like to know how to dynamically
> > access a column from the "OLD" variable. pk_col is the column name
> > from the table.
> >
> > I've come up with either doing this:
> > EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
>
> Got focused on pk_val = OLD[pk_col] and missed the low hanging fruit:
>
> pk_val = OLD.pk_col
>
> >
> > which looks a bit excessive, or this
> >
> > pk_val = to_jsonb(OLD.*)->pk_col
> >
> > which looks cleaner, but then I am having to incur a little overhead
> > by using the to_jsonb function. Ideally, something like this would be
> > great:
> >
> > pk_val = OLD[pk_col]
> >
> > but evidently we can't subscript ROW types.
> >
> > Am I missing out on a simpler or more elegant solution?
> >
> > Rhys
> > Peace & Love | Live Long & Prosper
> >
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: A Good Beginner's Book
Hi, On Sat, Aug 12, 2023 at 6:23 PM Miles Elam wrote: > > On 8/12/23 9:02 a.m., Amn Ojee Uw wrote: > > > > Is there a book to be recommended for PostgreSQL beginners? > I’m the author of Practical SQL from No Starch Press. My book combines an intro to SQL with lessons on data analysis. Learn more at https://practicalsql.com > > >
Re: A Good Beginner's Book
> On 8/12/23 9:02 a.m., Amn Ojee Uw wrote: > > Is there a book to be recommended for PostgreSQL beginners? If you are new to relational databases and SQL in general, I recommend the basics of SQL (not Postgres-specific) to start off. The SQL Murder Mystery is a good first experience. https://mystery.knightlab.com/ If you are not an SQL beginner but looking more for Postgres-specific knowledge, it depends on what area you're looking for: administration, performance, customization, etc. Here is the list provided from the Postgres web site with a range of areas and experience level for study. Some can be downloaded for free while others would be purchased. https://www.postgresql.org/docs/books/ I myself started with "PostgreSQL: Introduction and Concepts" many years ago and loved it. It's pretty long in the tooth now (20+ years old?!), but still good for SQL beginners in general and available free online. (Thanks again, Bruce Momjian!) The first one on the web page looks pretty good for SQL beginners too. "POSTGRES: The First Experience" Best wishes on your journey!
Re: Dynamically accessing columns from a row type in a trigger
On 8/12/23 13:09, Rhys A.D. Stewart wrote:
Greetings all,
I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.
I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
Got focused on pk_val = OLD[pk_col] and missed the low hanging fruit:
pk_val = OLD.pk_col
which looks a bit excessive, or this
pk_val = to_jsonb(OLD.*)->pk_col
which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:
pk_val = OLD[pk_col]
but evidently we can't subscript ROW types.
Am I missing out on a simpler or more elegant solution?
Rhys
Peace & Love | Live Long & Prosper
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Dynamically accessing columns from a row type in a trigger
On 8/12/23 13:09, Rhys A.D. Stewart wrote:
Greetings all,
I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.
I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
which looks a bit excessive, or this
pk_val = to_jsonb(OLD.*)->pk_col
which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:
pk_val = OLD[pk_col]
Well if want/can use plpython3u you can do just that:
https://www.postgresql.org/docs/current/plpython-trigger.html
as:
pk_val = TD["old"][pk_col]
but evidently we can't subscript ROW types.
Am I missing out on a simpler or more elegant solution?
Rhys
Peace & Love | Live Long & Prosper
--
Adrian Klaver
adrian.kla...@aklaver.com
Dynamically accessing columns from a row type in a trigger
Greetings all,
I am writing a trigger and would like to know how to dynamically
access a column from the "OLD" variable. pk_col is the column name
from the table.
I've come up with either doing this:
EXECUTE format('SELECT $1.%1$I', pk_col) INTO pk_val USING OLD;
which looks a bit excessive, or this
pk_val = to_jsonb(OLD.*)->pk_col
which looks cleaner, but then I am having to incur a little overhead
by using the to_jsonb function. Ideally, something like this would be
great:
pk_val = OLD[pk_col]
but evidently we can't subscript ROW types.
Am I missing out on a simpler or more elegant solution?
Rhys
Peace & Love | Live Long & Prosper
Re: A Good Beginner's Book
*Addendum* : I am using JDBC as the framework. On 8/12/23 9:02 a.m., Amn Ojee Uw wrote: Hello folks. Is there a book to be recommended for PostgreSQL beginners? Thanks in advance.
A Good Beginner's Book
Hello folks. Is there a book to be recommended for PostgreSQL beginners? Thanks in advance.
Re: PgSQL 15.3: Execution plan not using index as expected
Thanks Rob, no it's not a problem with the index. It's a problem with the use of CURRENT_USER in the WHERE I submitted a new post on this matter with a test case. Kind regards == Dürr Software Entw. Guggenberg 26, DE-82380 Peißenberg fon: +49-8803-4899016 fax: +49-8803-4899017 i...@fduerr.de Am 12.08.23 um 04:25 schrieb rob stone: Hello, - - PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.8.3-2) 4.8.3, 64-bit - PostgreSQL 15.3 (Debian 15.3-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit (1 Zeile) 9.3 plan -> Index Scan using client_session_user_id_idx on client_session Looks like a collation issue given the difference in compilers used. In the 9.3 plan the index is used. Maybe try a reindex of the table. HTH, Rob begin:vcard fn;quoted-printable:Franz D=C3=BCrr n;quoted-printable:D=C3=BCrr;Franz email;internet:i...@fduerr.de tel;work:08803-4899016 tel;fax:08803-4899017 tel;home:08803-489375 version:2.1 end:vcard
Re: PgSQL 15.3: Execution plan not using index as expected
Thanks Adrian,
sorry for the misunderstanding.
I ran ANALYZE, it didn't change a thing (as expected).
Anyway, I pinned the problem down now: It's the use of CURRENT_USER (or
SESSION_USER etc.) in the WHERE condition.
If i replace it with 'postgres' (the result of CURRENT_USER) the planner
works as expected..
The old 9.x - version of PgSQL didn't have that problem.
Test case:
-- our test table with index on user_id
CREATE TABLE tt (
user_id VARCHAR(63) NOT NULL DEFAULT SESSION_USER
);
CREATE INDEX tt_user_id_idx ON tt(user_id);
-- fill with test data
INSERT INTO tt(user_id) select 'U' || i from generate_series(1,10) as i;
INSERT INTO tt(user_id) select SESSION_USER from generate_series(1,100);
-- query using CURRENT_USER as WHERE-condition - doesn't use index
EXPLAIN ANALYZE SELECT * FROM tt WHERE user_id::character
varying(63)=CURRENT_USER::character varying(63) LIMIT 1;
QUERY PLAN
-
Limit (cost=0.00..21.65 rows=1 width=6) (actual time=18.143..18.143
rows=1 loops=1)
-> Seq Scan on tt (cost=0.00..2446.00 rows=113 width=6) (actual
time=18.141..18.141 rows=1 loops=1)
Filter: ((user_id)::text = ((CURRENT_USER)::character
varying(63))::text)
Rows Removed by Filter: 10
Planning Time: 0.154 ms
Execution Time: 18.163 ms
(6 Zeilen)
SELECT CURRENT_USER;
current_user
--
postgres
(1 Zeile)
-- query using result of CURRENT_USER as WHERE-condition - uses index
EXPLAIN ANALYZE SELECT * FROM tt WHERE user_id::character
varying(63)='postgres'::character varying(63) LIMIT 1;
QUERY PLAN
Limit (cost=0.42..0.47 rows=1 width=6) (actual time=0.018..0.019
rows=1 loops=1)
-> Index Only Scan using tt_user_id_idx on tt (cost=0.42..6.39
rows=113 width=6) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: (user_id = 'postgres'::text)
Heap Fetches: 0
Planning Time: 0.081 ms
Execution Time: 0.026 ms
(6 Zeilen)
-- CURRENT_USER is not expensive..
EXPLAIN ANALYZE SELECT CURRENT_USER;
QUERY PLAN
-
Result (cost=0.00..0.01 rows=1 width=64) (actual time=0.005..0.006
rows=1 loops=1)
Planning Time: 0.031 ms
Execution Time: 0.025 ms
(3 Zeilen)
I hope that this should clarify the problem.
Thanks and kind regards
==
Dürr Software Entw.
i...@fduerr.de
Am 11.08.23 um 16:32 schrieb Adrian Klaver:
On 8/11/23 03:11, Dürr Software wrote:
Please reply to list also
Ccing list
Dear Adrian,
thanks for the reply. Of course i ran ANALYZE on the 15.3 system, its
in the second part of my post, but here again, FYI:
That is EXPLAIN ANALYZE where it is an option to the command:
https://www.postgresql.org/docs/current/sql-explain.html
ANALYZE
Carry out the command and show actual run times and other
statistics. This parameter defaults to FALSE.
What I was talking about was the ANALYZE command:
https://www.postgresql.org/docs/current/sql-analyze.html
ANALYZE collects statistics about the contents of tables in the
database, and stores the results in the pg_statistic system catalog.
Subsequently, the query planner uses these statistics to help
determine the most efficient execution plans for queries.
test=# \d client_session
Tabelle
»client_session«
Spalte | Typ | Sortierfolge | NULL
erlaubt? | Vorgabewert
---++--+---+--
id | bigint | | not
null | nextval('admin.client_session_id_seq'::regclass)
tstamp_start | timestamp(3) without time zone | | not
null | now()
permit_id | character varying(63) | | not
null | "current_user"()
user_id | character varying(63) | | not
null | "session_user"()
Indexe:
"client_session_pkey" PRIMARY KEY, btree (id)
"client_session_user_id_idx" btree (user_id, tstamp_start DESC)
test=# explain analyze SELECT permit_id FROM client_session WHERE
user_id::character varying(63)=SESSION_USER::character varying(63)
ORDER BY tstamp_start DESC LIMIT 1;
QUERY
PLAN
---
Limit (cost=2852336.36..2852336.48 rows=1 width=23) (actual
Re: How to set default privilege for new users to have no access to other databases?
On 2023-08-09 14:35:40 -0400, Erik Nelson wrote: > I have a lab with a database that I would like to use as a "multi-tenant" > database, in that I would like to create a database for each of the > applications that I'm running and segregate access so that user foo and user > bar cannot see anything about their neighbors. I'm somewhat surprised to > discover that any new user, by default, has the ability to list databases, > connect to them, and list their tables. > > My understanding is that this ability is inherited from the public role (could > use confirmation of this)? I can think of two potential options, one being > more > desirable: > > • I know I can revoke CONNECT from an explicit database, but this requires > that I specify the database. I want to revoke this for all current, and > future databases as the default privilege. New databases are created as copies of a template database (template1 by default). You can either alter template1 to your liking or create a new template database and use that for creating your new databases (the latter is especially useful if you need several different templates). You could also use pg_hba.conf to restrict or grant access to specific databases. This would probably mean that you would have to add a line to pg_hba.conf each time you create a database. And of course if you use the same database schema for several applications you probably already have a script to set up a database. Adding one or more REVOKE and/or GRANT statements to such a script would seem to be a rather obvious way to do it. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
