Re: database designs ERDs

This most invaluable link on database designs is now broken. http://databaseanswers.org/data_models/index_all_models.htm Has it moved somewhere or is there a similar page ? Regards Zahid https://github.com/zahidr/zahidr https://www .backbutton.org ¯\_(ツ)_/¯ ♡۶♡ ۶♡۶

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > > As for "schema identifiers" vs. "schema names" - they both seem equally > wrong. The list can very much contain sequences of characters that when > interpreted as an identifier and looked for in the pg_namespace catalog do > not find a matching entry and

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 1:47 PM Bryn Llewellyn wrote: > > It seems that the wording is wrong here: > > « The value for search_path must be a comma-separated list of schema > names. » > > It's schema identifiers—and not schema names. Yes? > > To add further clarity (or confusion) there is also set_

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote: >> >> « >> A convention often used is to write key words in upper case and names in >> lower case, e.g.: >> >> UPDATE my_table SET a = 5; >> » >> >> It should be « to write key words in upper case and unquoted identifiers in >>

Re: Seems to be impossible to set a NULL search_path

On 7/6/22 14:47, Bryn Llewellyn wrote: The problem was my stupid typo: writing « set search_path = 'pg_catalog, pg_temp'; » when I should *not* have typed those single quotes. Now the demo that I'd intended gets the outcome that I'd expected: *select count(*) from pg_class; --<< 39

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > >> b...@yugabyte.com wrote >> ... > > You either didn't read or failed or retain knowledge of the words in the > documentation that are the canonical reference for search_path and explain > exactly this. I suggest you (re-)read them. > > https://www.postgr

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 1:13 PM Bryn Llewellyn wrote: > *david.g.johns...@gmail.com wrote:* > > At the level of discussion you want to have when you encounter unfamiliar > syntax please read the syntax chapter for the related concept (expression > identifiers). > > > https://www.postgresql.org/do

Re: lifetime of the old CTID

On 7/6/22 12:51, Matthias Apitz wrote: El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió: On 7/6/22 01:18, Matthias Apitz wrote: [snip] Ofc, each table has its own primary key(s), used for example for the SELECT ctid, * FROM d01buch WHERE ... As I said, we came to Postg

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > > At the level of discussion you want to have when you encounter unfamiliar > syntax please read the syntax chapter for the related concept (expression > identifiers). > > https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIER

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 11:50 AM Bryn Llewellyn wrote: > > It succeeded. And the \d metacommand showed me that I now have a table > pg_temp_3.x. Using a different database, it ends up in "pg_temp_1. What's > going on here? Is "pg_temp" a kind of generic nickname for ANY "pg_temp_N"? > > I hate to

Re: lifetime of the old CTID

> On Jul 6, 2022, at 12:51, Matthias Apitz wrote: > it is uniqu to identify a row in a table once > known. I think the point that we are trying to make here is that a ctid *isn't* that. There is no guarantee, at all, at any level, that the ctid of a row will remain stable, not even between

Re: lifetime of the old CTID

El día miércoles, julio 06, 2022 a las 02:33:42p. m. -0500, Ron escribió: > On 7/6/22 01:18, Matthias Apitz wrote: > [snip] > > Ofc, each table has its own primary key(s), used for example for the > > SELECT ctid, * FROM d01buch WHERE ... > > > > As I said, we came to PostgreSQL from Sybase (and

Re: lifetime of the old CTID

On 7/6/22 01:18, Matthias Apitz wrote: [snip] Ofc, each table has its own primary key(s), used for example for the SELECT ctid, * FROM d01buch WHERE ... As I said, we came to PostgreSQL from Sybase (and Oracle) and Sybase has for each table a so called SYB_IDENTITY_COLUMN which is static for the

Re: Multiple Indexes

On Thu, 7 Jul 2022 at 04:07, DAVID ROTH wrote: > I understand the planner can use multiple indexes to get the best plan. > Can someone point me a paper that explains how this works. I don't know of a paper, but if you're talking about using multiple indexes to scan a single relation in order to s

Re: Seems to be impossible to set a NULL search_path

> david.g.johns...@gmail.com wrote: > > unless you are going to write: operator(pg_catalog.=) in your function the > advice to always use schema qualifications is not going to be taken > seriously... the correct search_path to set isn't "empty" but "pg_catalog", > "pg_temp". While this does vi

Re: lifetime of the old CTID

> Said this, we can end this thread. Re-think the data model is not an > option. Why not ? - add a primary key to each table, say db01buch.pk - rename tables, say db01buch -> db01buch__real_table - add views, say db01buch over db1buch__real_table with "pk AS ctid" (untest

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 10:03 AM Christophe Pettus wrote: > > > > On Jul 6, 2022, at 09:48, Bryn Llewellyn wrote: > > Neither causes an error. The "show", in each case, prints the bare value > with no quotes. It never struck me try try double quotes around the > timezone argument. I'm shocked tha

Re: Seems to be impossible to set a NULL search_path

On Wed, Jul 6, 2022 at 9:49 AM Bryn Llewellyn wrote: > adrian.kla...@aklaver.com wrote: > > Not sure what your point is? > > > Try these two: > > > > > > > *set timezone = 'America/New_York';show timezone;set timezone = > "America/New_York";show timezone;* > Neither causes an error. The "show", i

Re: Seems to be impossible to set a NULL search_path

> On Jul 6, 2022, at 09:48, Bryn Llewellyn wrote: > Neither causes an error. The "show", in each case, prints the bare value with > no quotes. It never struck me try try double quotes around the timezone > argument. I'm shocked that they are silently accepted here and seem to have > the same

Re: Seems to be impossible to set a NULL search_path

> adrian.kla...@aklaver.com wrote: > > Not sure what your point is? Try these two: set timezone = 'America/New_York'; show timezone; set timezone = "America/New_York"; show timezone; Neither causes an error. The "show", in each case, prints the bare value with no quotes. It never struck me tr

Multiple Indexes

I understand the planner can use multiple indexes to get the best plan. Can someone point me a paper that explains how this works. Thanks

Re: lifetime of the old CTID

On Wed, Jul 06, 2022 at 02:26:00PM +0200, Matthias Apitz wrote: > El d??a Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert > escribi??: > > > Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz: > > [skipped] > WHERE-clause of its interest ("WHERE d01status=4"), here a

Re: Seems to be impossible to set a NULL search_path

On 7/5/22 20:55, Bryn Llewellyn wrote: // /david.g.johns...@gmail.com wrote: / t...@sss.pgh.pa.us  wrote: search_path's value is not a SQL name.  It's a list of SQL names wrapped in a string ... and the list can be empty. This d

Re: Seems to be impossible to set a NULL search_path

On Tue, Jul 5, 2022 at 11:12 AM Bryn Llewellyn wrote: > The section "Writing SECURITY DEFINER Functions Safely": > > > https://www.postgresql.org/docs/current/sql-createfunction.html#id-1.9.3.67.10.2 > > explains the risk brought if a bad actor creates an object that preemps > what the developer

Re: lifetime of the old CTID

El día miércoles, julio 06, 2022 a las 03:53:54p. m. +0200, Peter J. Holzer escribió: > On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote: > > DB layer must LOCK the row for update. It does so using the CTID. Of > > course there is a key in the row (d01gsi, the signature of the book), > > but th

Re: ADD COLUMN ts tsvector GENERATED too slow

Hi Florents: On Wed, 6 Jul 2022 at 12:35, Florents Tselai wrote: > 50M+ rows and iirc pg_relation_size was north of 80GB or so. Somebody already mentioned pg_table_size, but anyway this gives you 1.6Kb per row, which I would suspect is extremely low given your pdf content and the 1M truncation y

Re: lifetime of the old CTID

On 2022-07-06 14:26:00 +0200, Matthias Apitz wrote: > DB layer must LOCK the row for update. It does so using the CTID. Of > course there is a key in the row (d01gsi, the signature of the book), > but this is not uniqu and can't be used to lock exactly this row for > update. If it isn't unique it

Re: lifetime of the old CTID

On 2022-Jul-06, Matthias Apitz wrote: > This gives in the DB layer a CURSOR of say 100.000 rows of the > 3.000.000 in the table. Now the application fetches row by row and see > if something should be done with the row. If so, the DB layer must > LOCK the row for update. It does so using the CTID.

Re: lifetime of the old CTID

El día Mittwoch, Juli 06, 2022 a las 11:45:14 +0200, Karsten Hilbert escribió: > Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz: > > > > On first glance, it appears that you are using the ctid as a primary key > > > for a row, and that's highly not-recommended. The ctid is neve

Re: ADD COLUMN ts tsvector GENERATED too slow

On 2022-Jul-06, Florents Tselai wrote: > Actually, I monitored my disk usage and it was **definitely** working as > It had already eaten up an additional 30% of my disk capacity. Adding a column like this requires creating a second copy of the table, copying all the contents from the old table (

Re: ADD COLUMN ts tsvector GENERATED too slow

> On 6 Jul 2022, at 1:35 PM, Florents Tselai wrote: > > > >> On 6 Jul 2022, at 1:11 PM, Francisco Olarte wrote: >> >> On Wed, 6 Jul 2022 at 11:55, Florents Tselai >> wrote: >>> Also, fwiw looking at top the CPU% and MEM% activity, looks like it does >>> data crunching work. >> ... >

Re: ADD COLUMN ts tsvector GENERATED too slow

> On 6 Jul 2022, at 1:11 PM, Francisco Olarte wrote: > > On Wed, 6 Jul 2022 at 11:55, Florents Tselai > wrote: >> Also, fwiw looking at top the CPU% and MEM% activity, looks like it does >> data crunching work. > ... On 06.07.22 10:42, Florents Tselai wrote: > I have a beefy server

Re: ADD COLUMN ts tsvector GENERATED too slow

On Wed, 6 Jul 2022 at 11:55, Florents Tselai wrote: > Also, fwiw looking at top the CPU% and MEM% activity, looks like it does data > crunching work. ... > >> On 06.07.22 10:42, Florents Tselai wrote: > >>> I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a > >>> table hol

Re: unable to understand query result

On Wed, Jul 06, 2022 at 11:40:22AM +0200, Peter Eisentraut wrote: > On 06.07.22 11:31, Stefan Froehlich wrote: > >I have a database returing these result sets for two almost > >identical queries: > > > >#v+ > >$ select id, pid, length(pid), md5(pid) from product where pid like > >'800'; > >(1

Re: ADD COLUMN ts tsvector GENERATED too slow

Also, fwiw looking at top the CPU% and MEM% activity, looks like it does data crunching work. > On 6 Jul 2022, at 12:48 PM, Florents Tselai wrote: > > > >> On 6 Jul 2022, at 12:38 PM, Peter Eisentraut >> wrote: >> >> On 06.07.22 10:42, Florents Tselai wrote: >>> I have a beefy server (40+

Re: ADD COLUMN ts tsvector GENERATED too slow

> On 6 Jul 2022, at 12:38 PM, Peter Eisentraut > wrote: > > On 06.07.22 10:42, Florents Tselai wrote: >> I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a >> table holding (key text, text text,) of around 50M rows. >> These are text fields extracted from 4-5 page pdfs

Re: lifetime of the old CTID

Am Wed, Jul 06, 2022 at 08:41:58AM +0200 schrieb Laurenz Albe: > Using the primary key is the proper solution. To be safe from concurrent > modifications, use a logic like in this pseudo-code: > > FOR b IN SELECT pk, other_columns FROM books WHERE condition >UPDATE books SET ... WHERE pk = ..

Re: lifetime of the old CTID

Am Wed, Jul 06, 2022 at 08:18:42AM +0200 schrieb Matthias Apitz: > > On first glance, it appears that you are using the ctid as a primary key > > for a row, and that's highly not-recommended. The ctid is never intended > > to be stable in the database, as you have discovered. There are really

Re: unable to understand query result

On 06.07.22 11:31, Stefan Froehlich wrote: I have a database returing these result sets for two almost identical queries: #v+ $ select id, pid, length(pid), md5(pid) from product where pid like '800'; id | pid | length | md5 --+-++--

Re: ADD COLUMN ts tsvector GENERATED too slow

On 06.07.22 10:42, Florents Tselai wrote: I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a table holding (key text, text text,) of around 50M rows. These are text fields extracted from 4-5 page pdfs each. I’m adding the following generated col to keep up with tsvectors

unable to understand query result

I have a database returing these result sets for two almost identical queries: #v+ $ select id, pid, length(pid), md5(pid) from product where pid like '800'; id | pid | length | md5 --+-++-- 3594 | 800 | 7 | 60

ADD COLUMN ts tsvector GENERATED too slow

I have a beefy server (40+ worker processes , 40GB+ shared buffers) and a table holding (key text, text text,) of around 50M rows. These are text fields extracted from 4-5 page pdfs each. I’m adding the following generated col to keep up with tsvectors ALTER TABLE docs_text ADD COLUMN ts tsvect

Re: How to upgrade postgres version 8 to 13

On Tue, 2022-07-05 at 22:24 +0530, shashidhar Reddy wrote: > Could some please let me know how to upgrade postgresql version 8.2 to 13, > as the database size is 20 TB, backup and restore is not possible. Then you will probably have to use Slony, and pray to a deity of your choice. Yours, Laurenz