Re: Lookup tables

> On 4 Feb 2025, at 18:11, David G. Johnston wrote: > > On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek > wrote: >> >> >> > On 4 Feb 2025, at 15:27, Rich Shepard > > > wrote: >> > >> > Should lookup tables have a numeric FK column as

Re: Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL

On Tue, Feb 4, 2025 at 1:50 PM Ayush Vatsa wrote: > Also, what would be the best way to ensure that, by default, no roles > (except the function owner) have any privileges on new functions created in > my protected schema? > Create them in another schema altogether, then move it to my_schema once

Re: Lookup tables

Am Tue, Feb 04, 2025 at 10:41:38PM +0100 schrieb Thiemo Kellner: > >> On 4 Feb 2025, at 18:27, Thiemo Kellner > >> wrote: > >> > >>  Unless the lookup table is actually a check constraint one can use to > >> populate dropdown boxes in an interface. > > > > That is even worse because it ceases

Re: Lookup tables

04.02.2025 18:31:09 Michał Kłeczek : > >> On 4 Feb 2025, at 18:27, Thiemo Kellner wrote: >> >>  Unless the lookup table is actually a check constraint one can use to >> populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and users might > se

Re: old OS

https://ora2pg.darold.net/ It can access remote Oracle databases, and migrate LOB columns to bytea. Worked quite well for me on a database 3x that size. On Tue, Feb 4, 2025 at 3:28 PM Marc Millas wrote: > Hi Tom, > > the data in the redhat 6.5 machine is NOT in Postgres, its in an old > Oracle

Re: Lookup tables [FIXED]

On Tue, 4 Feb 2025, Rich Shepard wrote: I'll make new tables today. That did not work as well as I expected. Instead, I added a new column (type `serial') to each of the two lookup tables. That adds a PK to each while retaining the table and column names referenced by other tables. Thanks for

Re: old OS

On 2/4/25 12:27, Marc Millas wrote: Hi Tom, the data in the redhat 6.5 machine is NOT in Postgres, its in an old Oracle instance, 4TB of it. Maybe?: https://github.com/laurenz/oracle_fdw -- Adrian Klaver adrian.kla...@aklaver.com

Re: old OS

Marc Millas writes: > the data in the redhat 6.5 machine is NOT in Postgres, its in an old Oracle > instance, 4TB of it. > So I am working on a way to extract it to a file or, better to a pipe. > If I was able to get a local psql then, fine, i'll pipe the flow to it, > done.(almost...) > If no lo

Re: old OS

👍👍👍 thanks Marc MILLAS Senior Architect +33607850334 On Tue, Feb 4, 2025 at 7:04 PM Tim Gerber wrote: > Appears PG 12 is available for RHEL 6 via the following repository: > > https://download.postgresql.org/pub/repos/yum/12/redhat/ >

Re: old OS

Hi Tom, the data in the redhat 6.5 machine is NOT in Postgres, its in an old Oracle instance, 4TB of it. So I am working on a way to extract it to a file or, better to a pipe. If I was able to get a local psql then, fine, i'll pipe the flow to it, done.(almost...) If no local postgres tool, as s

Re: Table copy

On 2/4/25 11:51 AM, Andy Hartman wrote: I'm copying a large table from mssql to PG using SImplysql and in the Log I see this message 2025-02-04 14:42:17.975 EST [4796] ERROR:  unexpected EOF on client connection with an open transaction The above pretty much spells it out. Something mess

Table copy

I'm copying a large table from mssql to PG using SImplysql and in the Log I see this message 2025-02-04 14:42:17.975 EST [4796] ERROR: unexpected EOF on client connection with an open transaction 2025-02-04 14:42:17.975 EST [4796] CONTEXT: COPY sqlt_data_1_2022_03, line 208274199, column tagid 2

Re: Lookup tables

On Tue, 4 Feb 2025, David G. Johnston wrote: Is the use of FK here intentional or a typo? Sigh, typo. Should be PK. Rich

Re: Lookup tables

On Tuesday, February 4, 2025, Rich Shepard wrote: > > I want to replace the old lookup table (with no FK) with this one. > Is the use of FK here intentional or a typo? Because everything written so far leads me to believe it should be PK. Pri,are key is the unique side, Foreign key is the usag

Re: Lookup tables

On Tue, 4 Feb 2025, Rob Sargent wrote: Unless your lookup tables are huge I would create a new table matching your current table but with an identity column and load from you original table. I created a new table: create table ind_types_lu ( ind_nbr serial primary key, ind_name varchar(32

Re: Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL

On Tuesday, February 4, 2025, Ayush Vatsa wrote: > > postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE EXECUTE ON > FUNCTIONS FROM PUBLIC; > ALTER DEFAULT PRIVILEGES > As the documentation explains: Default privileges that are specified per-schema are added to whatever the global de

Re: Lookup tables

04.02.2025 18:31:09 Michał Kłeczek : >>  Unless the lookup table is actually a check constraint one can use to >> populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and users might > select something different than what they see on the screen.

Understanding ALTER DEFAULT PRIVILEGES Behavior in PostgreSQL

Hello PostgreSQL Community, I was experimenting with default privileges in PostgreSQL and came across a behavior I didn’t fully understand. I would appreciate any insights on this. I wanted to ensure that, by default, no roles had EXECUTE privileges on functions created in my schema. To achieve t

Re: Lookup tables

On Tuesday, February 4, 2025, Michał Kłeczek wrote: > > > On 4 Feb 2025, at 18:27, Thiemo Kellner > wrote: > > > >  Unless the lookup table is actually a check constraint one can use to > populate dropdown boxes in an interface. > > That is even worse because it ceases being transactional and u

Re: old OS

On 2/4/25 09:49, Marc Millas wrote: Hi, I need to install psql on a redhat 6.5 Linux, to transfert local data to a distant  postgreSQL 16 DB. Too my understanding, as this redhat version is EOL for long, No such psql is available. There are the PGDG archive repos: https://yum.postgresql.o

Re: old OS

Appears PG 12 is available for RHEL 6 via the following repository: https://download.postgresql.org/pub/repos/yum/12/redhat/ If you need more reference, take a look at: https://www.postgresql.org/download/linux/redhat/

Re: old OS

> On Feb 4, 2025, at 09:49, Marc Millas wrote: > My question is: is there any possibility to find something ? > and/or what is the latest psql available on a redhat 6.5 ? Although I've not done so, it should be possible to build from source even on a system that old.

Re: old OS

Christophe Pettus writes: > On Feb 4, 2025, at 09:49, Marc Millas wrote: >> My question is: is there any possibility to find something ? >> and/or what is the latest psql available on a redhat 6.5 ? > Although I've not done so, it should be possible to build from source even on > a system that

Re: Postgres performance issue with High CPU usage

Just validate your environment once via the below states ... -top -p $(pgrep -d',' postgres) -Review the PostgreSQL logs (typically located in /var/log/postgresql/ or /var/lib/pgsql/data/pg_log/) -- check if any long running active query running via below query SELECT pid, query, state, start_ti

Re: Lookup tables

Am Tue, Feb 04, 2025 at 06:30:53PM +0100 schrieb Michał Kłeczek: > > On 4 Feb 2025, at 18:27, Thiemo Kellner wrote: > > > >  Unless the lookup table is actually a check constraint one can use to > > populate dropdown boxes in an interface. > > That is even worse because it ceases being transact

old OS

Hi, I need to install psql on a redhat 6.5 Linux, to transfert local data to a distant postgreSQL 16 DB. Too my understanding, as this redhat version is EOL for long, No such psql is available. My question is: is there any possibility to find something ? and/or what is the latest psql available

Re: Lookup tables

> On 4 Feb 2025, at 18:27, Thiemo Kellner wrote: > >  Unless the lookup table is actually a check constraint one can use to > populate dropdown boxes in an interface. That is even worse because it ceases being transactional and users might select something different than what they see on th

Re: Lookup tables

Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface. Cheers Thiemo

Re: Postgres performance issue with High CPU usage

On 2/4/25 09:11, KK CHN wrote: List, Could someone point out how can I trace what causes the edb-postgres process with  %CPU usage reaching 73 to 80.1 percentage  in this box.  What's wrong with the server VM.. What version of EDB database are you using? If it is not their install of the

Re: Lookup tables

On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek wrote: > > > > On 4 Feb 2025, at 15:27, Rich Shepard wrote: > > > > Should lookup tables have a numeric FK column as well as the description > column? > > > > If so, how should I add an FK to the two lookup tables in my database? > > I’ve read the wh

Postgres performance issue with High CPU usage

List, Could someone point out how can I trace what causes the edb-postgres process with %CPU usage reaching 73 to 80.1 percentage in this box. What's wrong with the server VM.. The clients connecting the databases experience slow responses .. The top out put of the DB server is pasted below

Re: Lookup tables

On Tue, Feb 4, 2025 at 12:05 PM Rob Sargent wrote: > > > > On 2/4/25 10:03, Ron Johnson wrote: > > On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek wrote: > [snip] > >> >> The query to register a visit is: >> insert into restaurant_visit >> select $user, current_date, restaurant_id, $rating >> fro

Re: Lookup tables

> On 4 Feb 2025, at 17:51, Karsten Hilbert wrote: > > Am Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Michał Kłeczek: > >> It is now completely unclear what it means to change the name of the >> restaurant for already registered visits. >> Is it still the same restaurant with a different n

Re: Lookup tables

On 2/4/25 10:03, Ron Johnson wrote: On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek wrote: [snip] The query to register a visit is: insert into restaurant_visit select $user, current_date, restaurant_id, $rating from restaurant where name = $restaurant_name It is now co

Re: Lookup tables

On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek wrote: [snip] > > The query to register a visit is: > insert into restaurant_visit > select $user, current_date, restaurant_id, $rating > from restaurant where name = $restaurant_name > > > It is now completely unclear what it means to change the nam

Re: Lookup tables

On 2/4/25 09:51, Karsten Hilbert wrote: Am Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Michał Kłeczek: It is now completely unclear what it means to change the name of the restaurant for already registered visits. Is it still the same restaurant with a different name or a different resta

Re: Lookup tables

Am Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Michał Kłeczek: > It is now completely unclear what it means to change the name of the > restaurant for already registered visits. > Is it still the same restaurant with a different name or a different > restaurant? > > Or let say someone swaps na

Re: Index usage with differing string types

Henning Garus writes: > However when the String is cast to text the index isn't used: > explain select * from test where id = 'foo'::text; That's because "text" is considered a preferred type, so it wins the contest over whether '=' means texteq or bpchareq: # explain select * from test where id

Re: Lookup tables

> On 4 Feb 2025, at 15:27, Rich Shepard wrote: > > Should lookup tables have a numeric FK column as well as the description > column? > > If so, how should I add an FK to the two lookup tables in my database? I’ve read the whole thread and the reasoning for having (numeric) autogenerated s

Re: Index usage with differing string types

On 2/4/25 08:23, Henning Garus wrote: Hi, I stumbled upon this behaviour when digging into the performance of some merge statements generated by hibernate. Looking at different String types (varchar, text and bpchar) in some cases an index is used when the index type differs from the type in

Index usage with differing string types

Hi, I stumbled upon this behaviour when digging into the performance of some merge statements generated by hibernate. Looking at different String types (varchar, text and bpchar) in some cases an index is used when the index type differs from the type in the query, in some cases it isn't used. G

Re: Lookup tables

On 2/4/25 07:19, Ron Johnson wrote: How big is the database?  A tiny 500MB db just for you can get by with poor design.  (But then, why are you using PG instead of SQLite?) For the reasons listed here: https://sqlite.org/quirks.html In particular: [...] 2. SQLite Is Embedded, Not Client-Se

Re: Lookup tables

On Tue, Feb 4, 2025 at 9:28 AM Rich Shepard wrote: > Should lookup tables have a numeric FK column as well as the description > column? > Does your lookup table just have one column? (That's what your question seems to imply, but that makes no sense, since the whole point of a lookup table is t

Re: Lookup tables

On Tue, 4 Feb 2025, David G. Johnston wrote: It’s the FK side where the cost savings are experienced. David, Okay. Thanks, Rich

Re: Lookup tables

On Tue, 4 Feb 2025, Ron Johnson wrote: Does your lookup table just have one column? (That's what your question seems to imply, but that makes no sense, since the whole point of a lookup table is to store some sort of a code in the "child" table instead of the whole text of the description.) R

Re: Lookup tables

On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard wrote: > On Tue, 4 Feb 2025, David G. Johnston wrote: > > > The point of a lookup table is to provide a unique list of authoritative > > values for some purpose. Kinda like an enum. But having the label serve > as > > the unique value is reasonable - w

Re: Lookup tables

On Tuesday, February 4, 2025, Rich Shepard wrote: > On Tue, 4 Feb 2025, David G. Johnston wrote: > > The point of a lookup table is to provide a unique list of authoritative >> values for some purpose. Kinda like an enum. But having the label serve as >> the unique value is reasonable - we only a

Re: Lookup tables

On Tue, Feb 4, 2025 at 10:05 AM Rich Shepard wrote: > On Tue, 4 Feb 2025, Ron Johnson wrote: > > > Does your lookup table just have one column? (That's what your question > > seems to imply, but that makes no sense, since the whole point of a > lookup > > table is to store some sort of a code in

Re: Lookup tables

On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston wrote: > On Tuesday, February 4, 2025, Rich Shepard > wrote: > >> Should lookup tables have a numeric FK column as well as the description >> column? >> >> If so, how should I add an FK to the two lookup tables in my database? >> > > Most do (have

Re: Lookup tables

On Tuesday, February 4, 2025, Ron Johnson wrote: > On Tue, Feb 4, 2025 at 9:28 AM Rich Shepard > wrote: > >> Should lookup tables have a numeric FK column as well as the description >> column? >> > > Does your lookup table just have one column? (That's what your question > seems to imply, but t

Re: Lookup tables

On Tue, 4 Feb 2025, David G. Johnston wrote: The point of a lookup table is to provide a unique list of authoritative values for some purpose. Kinda like an enum. But having the label serve as the unique value is reasonable - we only add surrogates for optimization. David, The industrytypes t

Re: Lookup tables

On Tue, Feb 4, 2025 at 9:59 AM David G. Johnston wrote: > On Tuesday, February 4, 2025, Ron Johnson wrote: > >> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Tuesday, February 4, 2025, Rich Shepard >>> wrote: >>> Should lookup tables hav

Re: Lookup tables

On Tuesday, February 4, 2025, Ron Johnson wrote: > On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Tuesday, February 4, 2025, Rich Shepard >> wrote: >> >>> Should lookup tables have a numeric FK column as well as the description >>> column? >>> >>>

Re: Lookup tables

On Tue, 4 Feb 2025, David G. Johnston wrote: Most do (have a surrogate PK) since it removes cascading updates and is a smaller value. Lots of alter tables and update queries. David, That's a good point. Thanks, Rich

Re: Lookup tables

On Tue, 4 Feb 2025, Rob Sargent wrote: I would definitely add an Id for each of the looked up values. The code can use the Id (for the join or lookup) and the string value can safely be changed (think typo) without ripple effect.  It also prevents other tables from referencing the lookup witrh b

Re: Lookup tables

On Tuesday, February 4, 2025, Rich Shepard wrote: > Should lookup tables have a numeric FK column as well as the description > column? > > If so, how should I add an FK to the two lookup tables in my database? > Most do (have a surrogate PK) since it removes cascading updates and is a smaller va

Lookup tables

Should lookup tables have a numeric FK column as well as the description column? If so, how should I add an FK to the two lookup tables in my database? TIA, Rich