Re: psql: Add role's membership options to the \du+ command

On Fri, Mar 3, 2023 at 4:01 AM Pavel Luzanov wrote: > Hello, > > On 22.02.2023 00:34, David G. Johnston wrote: > > I didn't even know this function existed. But I see that it was changed in > 3d14e171 with updated documentation: > > https://www.postgresql.org/docs/d

Re: Converting row elements into a arrays?

On Thu, Mar 2, 2023 at 1:58 PM Ron wrote: > Postgresql 12.13 > > Given the sample below, I'm looking for how to generate this output. It's > like GROUP BY, but generating an array instead of an aggreate number. > > Group By creates groups, that's it. How you aggregate the data that are in

Re: Mistake in statement example

On Wed, Mar 1, 2023 at 9:34 AM Tom Lane wrote: > PG Doc comments form writes: > > I believe there is a mistake in an example on > > https://www.postgresql.org/docs/current/transaction-iso.html section > > 13.2.1: > > BEGIN; > > UPDATE accounts SET balance = balance + 100.00 WHERE acctnum =

Re: Move all elements toward another schema?

On Tue, Feb 28, 2023 at 9:37 AM celati Laurent wrote: > Good afternoon, > > With postgresql 13, I want to find a way to move 100 tables from schema > 'A' to schema 'B'. Not just data. But also indexes, primary keys, > constraints (INCLUDING ALL). > As far as i know, this piece of code would move

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

On Mon, Feb 27, 2023 at 6:22 PM Thorsten Glaser wrote: > > Not about the DISTINCTs. I haven’t used JOIN much (usually WHERE > as inner join) nor on tables this massive, and this is my second > foray into aggregate functions only. > > Fair. Consider this then as a jumping point to a more precise

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

On Mon, Feb 27, 2023 at 5:22 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > Lastly, if you do need to care about normalizing the output of JSON you > should consider writing a function that takes arbitrary json input and > reformats it, rather than trying to

Re: DISTINCT *and* ORDER BY in aggregate functions on expressions(!)

On Mon, Feb 27, 2023 at 4:11 PM mirabilos wrote: > > jsonb_build_object('opening_times', > jsonb_agg(DISTINCT jsonb_build_object( > 'weekday', cot.weekday, > 'from_hour', cot.from_hour, > 'to_hour', cot.to_hour) > ORDER BY cot.weekday, cot.from_hour, cot.to_hour)

Re: Tempory table is not getting created inside Function in postgres.

On Mon, Feb 27, 2023 at 1:17 PM nikhil raj wrote: > > The same query is executed outside the function its working properly > means just the qurey from drop temp table to insert but when i keep the > query inside a function its through me the error an after temp ra how > creation only i am using

Re: What's the prefix?

On Sun, Feb 26, 2023 at 9:16 AM jack...@gmail.com wrote: > use these sqls: > create table t(a text); > insert into t values('a'); > select lp,lp_len,t_data from heap_page_items(get_raw_page('t',0)); > lp | lp_len | t_data > ++ > 1 | 26 | \x0561 > as you can see, the 61

Re: Re: Give me more details of some bits in infomask!!

On Sun, Feb 26, 2023 at 8:36 AM jack...@gmail.com wrote: > > CID means "command ID" i.e. sequential ID assigned to commands in a > > single session (for visibility checks, so that a query doesn't see data > > deleted by earlier commands in the same session). See > >

Re: Re: Why the lp_len is 28 not 32?

On Sun, Feb 26, 2023 at 8:11 AM jack...@gmail.com wrote: > > *From:* Tomas Vondra > > > +++ > > 1 | 8160 | 28 | \x0100 > > > > > Pretty sure this is because we align the

Re: curiosity in default column header

On Fri, Feb 24, 2023 at 1:47 PM Rob Sargent wrote: > > Why is the last one headed "ascii" and not "bit"? > Because a function name is more likely to be a useful label than a data type. David J.

Re: psql: Add role's membership options to the \du+ command

On Tue, Feb 21, 2023 at 2:14 PM Pavel Luzanov wrote: > On 17.02.2023 19:53, David G. Johnston wrote: > > On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov > wrote: > >>List of roles >> Role name |

Re: transaction_isolation vs. default_transaction_isolation

On Tue, Feb 21, 2023 at 12:32 PM Bryn Llewellyn wrote: > I found a discussion with the same title as this emails’s subject here: > > https://postgrespro.com/list/thread-id/1741835 > > It dates from 2009. But it seems to be unresolved. The current PG doc here: > > 20.11. Client Connection

Re: MERGE examples not clear

On Tue, Feb 21, 2023 at 8:35 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-merge.html > Description: > > On this page: https://www.postgresql.org/docs/15/sql-merge.html > the first and second

Re: Thanks! Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

On Mon, Feb 20, 2023 at 12:57 PM Bryn Llewellyn wrote: > 3. Chapter 55 also has a section "Multiple Statements In A Simple Query". > But this feature seems to do no more semantically beyond implicitly > achieving what I could do by surrounding several statements explicitly with > "begin; ...

Re: pg_reload_conf()

On Mon, Feb 20, 2023 at 9:57 AM Marc Millas wrote: > > select pg_reload_conf(); > t > > So, I am perplexed: what pg_reload_conf() is doing/not doing ? > It is sending a signal and stating success that said signal was sent. David J.

Re: Improving inferred query column names

On Mon, Feb 20, 2023 at 8:08 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 11.02.23 20:24, Andres Freund wrote: > > > > I think on a green field it'd be clearly better to do something like the > > above. What does give me pause is that it seems quite likely to break > >

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

On Sat, Feb 18, 2023 at 4:49 PM Bryn Llewellyn wrote: > > And that the mode is a property of the current session. > To rephrase the other responses, the client-defined setting has no inherent relationship to the concept of a PostgreSQL session. How the client uses that setting is internal to

Re: unclear syntax explanation for multiple window definitions in a SELECT query

On Thu, Feb 16, 2023 at 10:18 AM Adam Mackler wrote: > > Maybe the reference section for SELECT is the wrong place for that longer > form, but section 4.2.8 (or some other referenced from there) could be. > > Expanding Section 7.2.5 to be more consistent in level-of-detail with the rest of that

Re: psql: Add role's membership options to the \du+ command

On Fri, Feb 17, 2023 at 4:02 AM Pavel Luzanov wrote: >List of roles > Role name | Attributes | > Member of > > ---++--- > admin |

Re: Vacuum full issue

On Thu, Feb 16, 2023 at 12:24 PM Rama Krishnan wrote: > One of my friend project they did vacuum full last week since one of the > table column data type were CLOB after that activity the dev team found out > some of the data were missing or corrupt (XML) , please explain me will > vacuum full

Re: How to avoid Trigger ping/pong / infinite loop

On Thu, Feb 16, 2023 at 10:43 AM Dominique Devienne wrote: > Are there techniques for situations like this? > > This question is not too far from my earlier question, in the sense that a > trigger would need to know the context in which it was triggered, i.e. > directly (then update the other

Re: DELETE trigger, direct or indirect?

On Thu, Feb 16, 2023 at 9:46 AM Adrian Klaver wrote: > On 2/16/23 05:23, Dominique Devienne wrote: > > Hi. This is a bit unusual. We have a foreign key between two tables, > > with ON DELETE CASCADE, to preserve referential integrity. But we > > apparently also need to preserve the severed

Re: unclear syntax explanation for multiple window definitions in a SELECT query

On Thu, Feb 16, 2023 at 5:13 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-select.html > Description: > > The syntax documentation for SELECT in SQL COMMANDS section is unclear in > explaining

Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

On Thu, Feb 16, 2023 at 8:48 AM cen wrote: > > - does the planner take previous runs of the same query and it's > execution time into account? If not, why? > No, because that isn't how it works. And while I'm no planner expert I'm not imagining any particularly compelling argument for why it

Re: psql: Add role's membership options to the \du+ command

On Wed, Feb 15, 2023 at 2:31 PM David Zhang wrote: > There is a default built-in role `pg_monitor` and the behavior changed > after the patch. If `\dg+` and `\du+` is treated as the same, and `make > check` all pass, then I assume there is no test case to verify the output > of `duS+`. My point

Re: Order of rows in simple "select r from table_fn()"

On Tue, Feb 14, 2023 at 4:49 PM Bryn Llewellyn wrote: > I've found that a table function with "returns table(r text)" provides a > convenient way to write a nicely formatted report using psql that can be > easily directed to a file with the "\o" metacommand. In general, for cases > like this, I

Re: Query plan for "id IS NULL" on PK

On Tue, Feb 14, 2023 at 3:25 PM Rob Sargent wrote: > > When will id be null in a primary key? > > The OP seems to be aware of this... "We cannot change the query being executed. Is there any way we can make the query planner ignore `OR (id IS NULL)` (as that will never be the case for the PK)

Re: pg_dump problem with postgres user

On Sun, Feb 12, 2023 at 7:22 AM wrote: > > I am using postgresql 14.6. PostgreSQL 15.1 is also installed in my system. > I just recognize that my scheduled backups are failing. > > I am using postgres user for backup user and .pgpass file for no password > prompt. > Given that you have

Re: psql: Add role's membership options to the \du+ command

On Fri, Feb 10, 2023 at 2:08 PM David Zhang wrote: > > I noticed the document psql-ref.sgml has been updated for both `du+` and > `dg+`, but only `du` and `\du+` are covered in regression test. Is that > because `dg+` is treated exactly the same as `du+` from testing point of > view? > Yes. >

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys wrote: > > > On 9 Feb 2023, at 16:41, Dominique Devienne wrote: > > > > Hi. We are implementing an API which takes a list of row keys, and must > return info about those rows. To implement that efficiently, in as few > round-trips as possible, we

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 9:09 AM Dominique Devienne wrote: > On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne > wrote: > >> On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Thu, Feb 9, 2023 at 8:41 AM D

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne wrote: > Hi. We are implementing an API which takes a list of row keys, and must > return info about those rows. To implement that efficiently, in as few > round-trips as possible, we bind a (binary) array of keys (ints, uuids, or > strings) and

Re: Using PostgreSQL for service discovery and health-check

On Thu, Feb 9, 2023 at 8:30 AM Dominique Devienne wrote: > > That's basically my plan. Now come the questions: > 1) will updating a row every second (for example) create issues? > 2) if yes to #1 above, what would be good mitigation tactics? Use > different table for service vs heartbeat?

Re: Domain check taking place unnecessarily?

On Wed, Feb 8, 2023 at 11:01 AM Mark Hills wrote: > > CREATE DOMAIN hash AS text > CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$'); > > devstats=> ALTER TABLE invite ADD COLUMN test hash; > ALTER TABLE > Time: 30923.380 ms (00:30.923) > Necessarily, I presume because if you decided that the check on

Re: Index scan for PK constraint validation

On Wednesday, February 8, 2023, Philippe VIEGAS wrote: > Hi all, > > I was wondering why the index statistics usage were not reflecting the > index usage for primary key constraint validation ? > > When we create a table with a primary key, PostgreSQL creates a Btree > index for the validation

Re: Quoting issue from ODBC

On Tue, Feb 7, 2023 at 5:20 PM Brad White wrote: > For example, this is the literal code in VBA > > Access: connection.Execute "UPDATE [" & strTable & "] SET [" & strTable & > "].[InsertFlag] = Null" _ > & " WHERE ((([" & strTable & "].[InsertFlag])=" & lngCurrUID & "));", > , adCmdText Or

Re: How do a user-defined function that returns a table executes a query?

On Tue, Feb 7, 2023 at 4:49 PM Katsuya Okizaki wrote: > In a normal SQL, we can use the EXPLAIN command to view the execution > plan. However, in this case, I am not sure how a user-defined function work. > > If anyone has faced a similar situation and found a way to view the > execution plan, I

Re: plpgsql: ambiguous column reference in ON CONFLICT clause

On Monday, February 6, 2023, Torsten Förtsch wrote: > > After reading this I am wondering if the current behavior is actually a > bug. > Arguably it is a bug, and a known one at that if you want some light reading, but regardless there is presently no proposal to get rid of the POLA violation

Re: pg_dump versus hash partitioning

On Wed, Feb 1, 2023 at 3:38 PM Tom Lane wrote: > Peter Geoghegan writes: > > You mentioned "minor releases" here. Who said anything about that? > > I did: I'd like to back-patch the fix if possible. I think changing > the default --load-via-partition-root choice could be back-patchable. > > If

Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?

On Wednesday, February 1, 2023, Ron wrote: > > > https://www.postgresql.org/docs/12/sql-select.html > > The docs say that one of these are required in the SELECT list. > > [ * | *expression* [ [ AS ] *output_name* ] [, ...] ] > > The square brackets you show are how optional elements are

Re: "SELECT FROM foo" acts like "SELECT COUNT(*) FROM foo"?

On Wednesday, February 1, 2023, Ron wrote: > > test=# select from sales_detail; > -- > (11 rows) > It returned 11 rows as per the psql output text, if it acted like count(*), an aggregate, it would have only returned one row. You have produced an 11-row, 0-column, output table. It isn’t

Re: VACUUM vs VACUUM FULL (was: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches)

On Wed, Feb 1, 2023 at 11:27 AM Dimitrios Apostolou wrote: > I have now run simple VACUUM but it didn't change anything, the simple > SELECT is still slow. > > My understanding by reading the docs is that it should reclaim all unused > space, just not return it to the OS. Which is fine by me.

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

On Wed, Feb 1, 2023 at 11:15 AM Dimitrios Apostolou wrote: > On Tue, 31 Jan 2023, David G. Johnston wrote: > > > > It feels like there is room for improvement here using table statistics > and the visibility map to significantly reduce the number of pages > retrieved that tu

Re: invisible commit question for sync replication

On Wednesday, February 1, 2023, Julien Rouhaud wrote: > Hi, > > On Wed, Feb 01, 2023 at 02:52:49PM +0800, qihua wu wrote: > > When run a cluster with sync replication, if DML is done on primary, but > > primary is isolated from all slave, then the DML will hang, if cancel it > > DML, it will

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

On Tue, Jan 31, 2023 at 8:28 AM Tom Lane wrote: > Dimitrios Apostolou writes: > > The question is why this simple query is taking so long to complete. > > Do you get the same 10 rows when you repeat the command? > > On the basis of the limited info you provided, I'm going to guess that > > (1)

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou wrote: > -> Seq Scan on public.test_runs_raw (cost=0.00..9250235.80 > rows=317603680 width=42) (actual time=745910.672..745910.677 rows=10 > loops=1) > Output: run_n, test_name_n, workitem_n, started_on, duration_ms, >

Re: How is timeout implemented in postgresql?

On Mon, Jan 30, 2023 at 6:36 PM qihua wu wrote: > I tried to find out how timeout, such as statement_timeout, works in > source code, But there are so many places it appears in the code, anybody > could show me some clue about where is the entry point for timeout > implementation? >

Re: How to control pg_catalog results for each users?

On Sunday, January 29, 2023, hirose shigeo(廣瀬 繁雄 □SWC○ACT) < shigeo.hir...@toshiba.co.jp> wrote: > > Has there been any discussion or development on controlling this system > catalog information on a per-user > I found this one:

Re: nextval per counted

On Fri, Jan 27, 2023 at 2:25 PM Rob Sargent wrote: > On 1/27/23 14:20, David G. Johnston wrote: > > On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent wrote: > >> I'm trying to craft SQL to invoke a sequence nextval once per grouped >> value. >> >> > This see

Re: nextval per counted

On Fri, Jan 27, 2023 at 1:59 PM Rob Sargent wrote: > I'm trying to craft SQL to invoke a sequence nextval once per grouped > value. > > This seems like a very unusual usage of nextval/sequences... with cleanup as ( select DISTINCT e.ma, coalesce(e.pa, 'fix') as pa from ... ), compute as (

Re: Set arbitrary GUC options during initdb

On Fri, Jan 27, 2023 at 8:53 AM Tom Lane wrote: > Robert Haas writes: > > The idea is that instead of: > > > replace_token(conflines, "#max_connections = 100", repltok); > > > You'd write something like: > > > replace_guc_value(conflines, "max_connections", repltok); > > > Which would look for

Re: CREATE ROLE bug?

On Wed, Jan 25, 2023 at 7:35 AM Bruce Momjian wrote: > > So, how would someone with CREATEROLE permission add people to their own > role, without superuser permission? Are we adding any security by > preventing this? > > As an encouraged design choice you wouldn't. You'd create a new group and

Re: Making Vars outer-join aware

On Tue, Jan 24, 2023 at 1:25 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Tue, Jan 24, 2023 at 12:31 PM Tom Lane wrote: > >> select ... from t1 left join t2 on (t1.x = t2.y and t1.x = 1); > >> > >> If we turn the generic equivclass.c log

Re: Naming of network_ops vs. inet_ops for SP-GIST

Michael, please see below: On Tue, Jan 24, 2023 at 12:44 PM PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html > Description: > > I wanted to add an

Re: gen_random_uuid is only available with pgcrypto enabled

On Tue, Jan 24, 2023 at 12:43 PM PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/functions-uuid.html > Description: > > Regarding the documentation on this page: >

Re: https://www.postgresql.org/account/comments/new/15/sql-alterdatabase.html/

On Tue, Jan 24, 2023 at 12:43 PM PG Doc comments form < nore...@postgresql.org> wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-alterdatabase.html > Description: > > Would love to see more information about each of the

Re: Making Vars outer-join aware

On Tue, Jan 24, 2023 at 12:31 PM Tom Lane wrote: > I wrote: > > Hans Buschmann writes: > >> I just noticed your new efforts in this area. > >> I wanted to recurr to my old thread [1] considering constant > propagation of quals. > >> [1] >

Re: Link WAL record to session

On Tue, Jan 24, 2023 at 12:30 PM kaido vaikla wrote: > Hi > Take a look at parameter > log_line_prefix > >> >> Really? Care to explain in more detail. I wouldn't expect that setting to have anything to do with WAL. David J.

Re: psql: Add role's membership options to the \du+ command

On Mon, Jan 9, 2023 at 9:09 AM Pavel Luzanov wrote: > When you include one role in another, you can specify three options: > ADMIN, INHERIT (added in e3ce2de0) and SET (3d14e171). > > For example. > > CREATE ROLE alice LOGIN; > > GRANT pg_read_all_settings TO alice WITH ADMIN TRUE, INHERIT TRUE,

v16 GRANT role TO role needs a multi-option setting capability

Hey, GRANT role_name [, ...] TO role_specification [, ...] [ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ] [ GRANTED BY role_specification ] It would be really nice to complete this new feature of INHERIT/SET FALSE/TRUE with a multi-specification capability. GRANT role_name

Re: Documentation discrepancy UNIQUE NULLS NOT DISTINCT

On Thu, Jan 19, 2023 at 3:41 PM Adrian Klaver wrote: > From here: > > https://www.postgresql.org/about/featurematrix/detail/392/ > > UNIQUE NULLS NOT DISTINCT > > By default, NULL values are not treated as distinct entries. Specifying > NULLS NOT DISTINCT on unique indexes / constraints will

Re: [DOCS] Stats views and functions not in order?

On Wed, Jan 18, 2023 at 8:38 AM Tom Lane wrote: > "David G. Johnston" writes: > > ... I was going for the html effect > > of having these views chunked into their own pages, any other changes > being > > non-detrimental. > > But is that a result

Re: [DOCS] Stats views and functions not in order?

On Wed, Jan 18, 2023 at 3:36 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 11.01.23 07:11, Peter Smith wrote: > > v9-0003 --> v10-0001 > > > >> I'm not sure if anything is pending for v9-0003, if there is something > >> pending, please post an updated patch for the same. >

Re: Maintaining blank lines in psql output?

On Tue, Jan 17, 2023 at 4:07 PM raf wrote: > On Tue, Jan 17, 2023 at 02:22:22PM -0700, "David G. Johnston" < > david.g.johns...@gmail.com> wrote: > > > On Tue, Jan 17, 2023 at 1:48 PM Ron wrote: > > > > > > > > White space can of course m

Re: Maintaining blank lines in psql output?

On Tue, Jan 17, 2023 at 1:48 PM Ron wrote: > > White space can of course make things easy to read, but psql seems to > ignore > those blank lines. Is there any way to retain them in psql output? > > Nope, there is no setting for psql to print all blank lines it encounters to stdout. If you

Re: Tablespace OID, database OID, relfilenode

On Tue, Jan 17, 2023 at 8:36 AM Wiwwo Staff wrote: > Hi Laurenz, > thanks for your answer! > Ok, but I don't think "1663" is a sort of constant value for "base", isn't > it? > > Let me clarify my question: how do I get, with a query, from "base" to > 1663 (or whatever the value is)? > >> >> The

Re: No function matches the given name and argument types.

On Mon, Jan 16, 2023 at 10:42 AM arons wrote: > Why the error happen is clear to me, in the example is also easy to see > that the 7th parameter is the problem. > But I'm searching a more general way to find easily which of the parameter > is the problem. > Suppose you have a function with 30

Re: pgsql: Add new GUC createrole_self_grant.

On Monday, January 16, 2023, Robert Haas wrote: > > > I don't really think there's too much wrong with what I wrote in the > patch as proposed, and I would like to get it committed and move on > without getting drawn into a wide-ranging discussion of every way in > which we might be able to

Re: If there are more than two functions in different schemas, the functions have the same name and same arguments, \df[+] only display the function that schema first appeared in the search_path.

On Sunday, January 15, 2023, 金 wrote: > > postgres=# \df fun1 > > List of functions > > Schema | Name | Result data type |Argument data types > | Type > > +--+--+- >

Re: does refreshing materialized view make the database bloat?

On Sunday, January 15, 2023, jian he wrote: > > > In the following example, I cannot see bloat (via extension pgstattuple > dead_tuple_count>0). Wondering where the bloat is. > > do the update. > update tbt set a = 10 + a where a < 20; > REFRESH MATERIALIZED view tbtmv; > SELECT * FROM

Re: does refreshing materialized view make the database bloat?

On Sunday, January 15, 2023, jian he wrote: > > > Hi, > why the materialized view itself bloats. If no refresh then no bloat > right? If fresh then set based delete operation will make materialized view > bloat? > I also found the same question online. https://dba.stackexchange.com/ >

Re: pgsql: Add new GUC createrole_self_grant.

On Sat, Jan 14, 2023 at 6:12 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > While the function owner has their own pg_db_role_setting preference for > this setting, > Should we be pointing out that if the role with CREATEROLE isn't also a LOGIN role then there

Re: pgsql: Add new GUC createrole_self_grant.

On Sat, Jan 14, 2023 at 5:31 PM Robert Haas wrote: > On Fri, Jan 13, 2023 at 8:29 PM David G. Johnston > wrote: > >> The point of the security definer section is to explain how to safely > write > >> security definer functions that you grant to less privileged users

Re: does refreshing materialized view make the database bloat?

On Friday, January 13, 2023, jian he wrote: > > Once we have the diff table, we perform set-based DELETE and INSERT >> operations against the materialized view, and discard both temporary >> tables. >> > > Here the temporary tables are "discard" meaning the temporary tables are > deleted and the

Re: pgsql: Add new GUC createrole_self_grant.

On Fri, Jan 13, 2023 at 4:46 PM Andres Freund wrote: > > I don't really see what that has to do with the topic at hand, unless you > want > to suggest removing the entire section about how to write secure security > definer functions? > Not remove, but I'm not seeing why the introduction of

Re: Intervals and ISO 8601 duration

On Fri, Jan 13, 2023 at 3:41 PM Adrian Klaver wrote: > On 1/13/23 14:17, Ken Tanzer wrote: > > On Thu, Jan 12, 2023 at 7:08 AM Tom Lane > > wrote: > > > > What Postgres actually stores for an interval is three fields: > > months, days, and microseconds. > > >

Re: Directly embedding a psql SET variable inside another string?

Please don't top-post. On Fri, Jan 13, 2023 at 10:08 AM Adam Scott wrote: > Do you mean like this? > > postgres=# \set v_embed %:v_ssn% > postgres=# \echo :v_embed > > test=# \set v_like %:v_ssn% >> test=# \echo :v_like >> >> Aside from the names those look identical... David J.

Re: Directly embedding a psql SET variable inside another string?

On Fri, Jan 13, 2023 at 9:12 AM Ron wrote: > > is there any way to *directly* embed v_ssn in another string? > No As expected, this fails: postgres=# SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; > ERROR: syntax error at or near ":" > LINE 1: SELECT * FROM employee WHERE ssn LIKE

Re: pgsql: Add new GUC createrole_self_grant.

On Thu, Jan 12, 2023 at 8:11 AM Robert Haas wrote: > On Wed, Jan 11, 2023 at 7:53 PM David G. Johnston > wrote: > > Justed wanted to chime in and say Robert has eloquently put into words > much of what I have been thinking here, and that I concur that guiding the &g

Re: gexec from command prompt?

On Thu, Jan 12, 2023 at 10:34 AM Pavel Stehule wrote: > > čt 12. 1. 2023 v 18:25 odesílatel Ron napsal: > >> >> Removing "\\exec" from the statement, and appending -c "\\gexec" to the >> psql >> command technically worked, but did not run the commands. >> > > I don't know why, but \g* commands

Re: Named Operators

On Thu, Jan 12, 2023 at 10:14 AM Tom Lane wrote: > Isaac Morland writes: > > What about backticks (`)? They are allowed as operator characters but do > > not otherwise appear in the lexical syntax as far as I can tell: > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html > >

Re: Named Operators

On Thu, Jan 12, 2023 at 3:59 AM Gurjeet Singh wrote: > On Thu, Jan 12, 2023 at 1:49 AM Matthias van de Meent > wrote: > > > I'm -1 on the chosen syntax; :name: shadows common variable > > substitution patterns including those of psql. > > I'll consider using one of the other special characters.

Re: pgsql: Add new GUC createrole_self_grant.

On Wed, Jan 11, 2023 at 2:16 PM Robert Haas wrote: > On Wed, Jan 11, 2023 at 4:00 PM Tom Lane wrote: > > Robert Haas writes: > > > If you want to make safe a SECURITY DEFINER function written using sql > > > or plpgsql, you either have to schema-qualify every single reference > > > or, more

Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode

On Wed, Jan 11, 2023 at 2:39 PM Andres Freund wrote: > Hi, > > On 2023-01-11 16:18:34 -0500, Tom Lane wrote: > > Peter Geoghegan writes: > > > On Wed, Jan 11, 2023 at 11:18 AM Andres Freund > wrote: > > >> I don't like that - it's also quite useful to disable use of > ringbuffers when > > >>

Re: 8.5.2 "integral" - "integer"

On Saturday, January 7, 2023, Tom Lane wrote: > PG Doc comments form writes: > > I think, the "integer" will be good choice instead "integral'": > > 8.5.2 > > ... > > "The offset will be shown as hh (hours only) if it is an integral number > of > > hours, else as hh:mm if it is an integral

Re: Resolve UNKNOWN type to relevant type instead of text type while bulk update using values

Please don’t top-post On Wednesday, January 4, 2023, Sayyid Ali Sajjad Rizavi wrote: > Breaking working queries for this is not acceptable. > > > Good point, let's exclude Option 2. > > >> This happens when possible so any remaining cases are not possible. Or, >> at least apparently not worth

Re: Resolve UNKNOWN type to relevant type instead of text type while bulk update using values

On Wednesday, January 4, 2023, Sayyid Ali Sajjad Rizavi wrote: > > > *Option 1:* Cast to the relevant column type in that position (to > `integer` in this case), whenever we have an unknown type. > This happens when possible so any remaining cases are not possible. Or, at least apparently not

Re: Updating column default values in code

On Wednesday, December 28, 2022, Brad White wrote: > > On timestamp fields, I need to update the column default from the current > "Now()" to "LOCALTIMESTAMP(0)" > > I could just manually make the change on every table, but then we would > still fail if we ever needed to restore a database. So I

Re: How to write a crosstab which returns empty row results

On Sun, Dec 25, 2022 at 12:25 AM David Goldsmith wrote: > ( > > > ''9adfe0ee-af21-4ec7-a466-c89bbfa0f750'' > > , > ''8714b2e3-d7ba-4494-a3ed-99c6d3aa2a9c'' > > , >

Re: GROUP BY ALL

On Sunday, December 18, 2022, Tom Lane wrote: > Andrey Borodin writes: > > I saw a thread in a social network[0] about GROUP BY ALL. The idea seems > useful. > > Isn't that just a nonstandard spelling of SELECT DISTINCT? > > What would happen if there are aggregate functions in the tlist? > I'm

Re: Postgres Date Type Value

On Sat, Dec 17, 2022 at 4:55 PM Michael Arnold wrote: > How do I interpret the 4 bytes of postgresql 'date' value? > See date2j and j2date in datetime.c timestamp.h has the relevant constant Tom refers to (POSTGRES_EPOCH_JDATE) David J.

Re: Test if a database has any privilege granted to public

On Thu, Dec 15, 2022 at 5:17 PM Bryn Llewellyn wrote: > > There's no mention on the "Privileges" page of the > "has_database_privilege()" function. Nor of "aclexplode()". > Even now, I haven't managed a linear start to finish read of the entire PG > docs. And I found "has_database_privilege()"

Re: Test if a database has any privilege granted to public

On Thu, Dec 15, 2022 at 12:51 PM Bryn Llewellyn wrote: > > > > > > *select datname::textfrom pg_databasewhere 0::oid = any(select > (aclexplode(datacl)).grantee)or datacl is null;* > That's easy if you know that you need to write this. But the need to do so > seems to depend on pretty arcane

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

On Thu, Dec 15, 2022 at 8:49 AM Tom Lane wrote: > Masahiko Sawada writes: > > I don't think we need additional PG_TRY() for that since exec_stmts() > > is already called in PG_TRY() if there is an exception block. I meant > > to call stmt_end() in PG_CATCH() in exec_stmt_block() (i.e. only when

Re: 'value' has special behaviour in alter system

On Thu, Dec 15, 2022 at 4:45 AM PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/15/sql-altersystem.html > Description: > > Documentation say > ALTER SYSTEM SET configuration_parameter { TO | = } { value |

Re: compiling postgres on windows - how to deal with unmatched file extension?

On Tue, Dec 13, 2022 at 9:56 PM Yang, T. Andy wrote: > I checked out source code from the latest master version on github. I was > expecting the build would go smoothly since I didn't modify anything. > That philosophy unfortunately doesn't work here. There are many pre-requisites and there

Re: Ordering behavior for aggregates

On Tue, Dec 13, 2022 at 9:45 AM Ronan Dunklau wrote: > Le mardi 13 décembre 2022, 16:13:34 CET Tom Lane a écrit : > > Accordingly, I find nothing at all attractive in this proposal. > > I think the main thing it'd accomplish is to drive users back to > > the bad old days of ordering-by-subquery,

Re: Regular expression for lower case to upper case.

On Sat, Dec 10, 2022 at 6:32 AM Peter J. Holzer wrote: > On 2022-12-10 11:00:48 +, Eagna wrote: > > > RegExp by itself cannot do this. You have to match all parts of the > > > input into different capturing groups, then use lower() combined > > > with format() to build a new string. Putting

<    2   3   4   5   6   7   8   9   10   11   >