Re: Disk wait problem... may not be hardware...

2023-10-27 Thread Peter J. Holzer
be different, of course. 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

Re: pg_checksums?

2023-10-28 Thread Peter J. Holzer
d then do the other one? I don't think so. AFAIK Replication keeps the data files in sync on a bit-for-bit level and turning on checksums changes the data layout. Running a cluster where one node has checksums and the other doesn't would result in a complete mess. hp -- _ |

Re: pg_checksums?

2023-10-29 Thread Peter J. Holzer
On 2023-10-29 10:11:07 +0100, Paul Förster wrote: > On Oct 29, 2023, at 02:43, Peter J. Holzer wrote: > > I don't think so. AFAIK Replication keeps the data files in sync on a > > bit-for-bit level and turning on checksums changes the data layout. > > Running a

Re: Disk wait problem... may not be hardware...

2023-10-29 Thread Peter J. Holzer
On 2023-10-27 19:46:09 -0400, p...@pfortin.com wrote: > On Fri, 27 Oct 2023 19:07:11 +0200 Peter J. Holzer wrote: > >Have you looked at the query plans as I recommended? (You might also > >want to enable track_io_timing to get extra information, but comparing > >just the qu

Re: [SOLVED?] Re: Disk wait problem... not hardware...

2023-10-29 Thread Peter J. Holzer
the real row count? 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

Re: pg_checksums?

2023-10-29 Thread Peter J. Holzer
On 2023-10-29 16:15:37 +0100, Paul Förster wrote: > On Oct 29, 2023, at 11:49, Peter J. Holzer wrote: > > It *might* work if there are zero writes on the primary during the > > downtime of the replica (because those writes couldn't be replicated), > > but that seems h

Re: [SOLVED?] Re: Disk wait problem... not hardware...

2023-10-30 Thread Peter J. Holzer
On 2023-10-29 12:45:08 -0400, p...@pfortin.com wrote: > On Sun, 29 Oct 2023 16:16:05 +0100 Peter J. Holzer wrote: > >However, the table statistics contain an estimate for the number of > >rows: > > > >hjp=> select schemaname, relname, n_live_tup from pg_stat_u

Re: pg_checksums?

2023-10-30 Thread Peter J. Holzer
> - Start the previous primary to be a standby of the node you failed > over to. I stand corrected. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative wri

Re: pg_checksums?

2023-10-30 Thread Peter J. Holzer
l corrupt the replica. > > Trying it would tell you something. > > > That's why I asked if I need to perform a patronictl reinit. > > Best to ask Percona. Why Percona? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |

Re: Postgres limitation in user management

2023-11-04 Thread Peter J. Holzer
y ALTER TABLE > to perform any troubleshooting in the database. This seems strange to me. What kind of troubleshooting requires to ability to ALTER TABLE but not to do DML? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) |

Re: Postgres limitation in user management

2023-11-04 Thread Peter J. Holzer
a in-house IT, who are not DBA's and have > no access to data. This doesn't answer the question why ALTER TABLE privilege would be required. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |

Re: Can user specification of a column value be required when querying a view ?

2023-11-24 Thread Peter J. Holzer
be accessed in a single query. 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

Re: Can user specification of a column value be required when querying a view ?

2023-11-25 Thread Peter J. Holzer
On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer wrote: > On 2023-11-20 22:03:06 -0500, Ron Johnson wrote: > > Or row level security. > > Does that help here? AIUI row level security can be used to limit access >

Re: Can user specification of a column value be required when querying a view ?

2023-11-25 Thread Peter J. Holzer
On 2023-11-25 10:49:56 -0500, Ron Johnson wrote: > On Sat, Nov 25, 2023 at 4:49 AM Peter J. Holzer wrote: > On 2023-11-24 13:06:45 -0500, Ron Johnson wrote: > > On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer > wrote: > >     On 2023-11-20 22:03:06 -05

Re: replication primary writting infinite number of WAL files

2023-11-26 Thread Peter J. Holzer
ng. If the database writes 1.5 GB/s of WALs and max_wal_size is the default of 1GB, shouldn't there be a checkpoint about every 0.7 seconds instead of just every 22 seconds? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | |

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

2023-11-26 Thread Peter J. Holzer
t just IP addresses. So now that you have IP addresses again, are there any for which a reverse lookup doesn't work? 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

Re: Store PDF files in PostgreDB

2023-12-08 Thread Peter J. Holzer
may not be fast enough. Another measure of "efficiency" might be how easy it is to use. Here, bytea fields are very nice: They act just like varchar fields, no special functions necessary. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) |

Re: Read write performance check

2023-12-19 Thread Peter J. Holzer
ot be very indicative of real performance. 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

Re: How to generate random bigint

2023-12-21 Thread Peter J. Holzer
e calls to random()) 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

Re: Help understanding server-side logging (and more...)

2023-12-21 Thread Peter J. Holzer
ens, one can connect to the DB from a shell (that > cluster has a single DB) w/o issues, and run queries just fine If you do that, do you see the "hanging" queries in pg_stat_activity? If so, what are they waiting for? hp -- _ | Peter J. Holzer| Story must mak

Re: Building Extension on Linux fails with relocation error

2023-12-22 Thread Peter J. Holzer
uild completes successfully when the .a file is > > smaller* (around 100 MB). > > Pure luck I suspect. I seem to remember a 256MB limit for position independent code on x86. The current man-page for GCC doesn't mention such a limit, though, so I may be mistaken. hp -- _

Re: Changing a schema's name with function1 calling function2

2023-12-24 Thread Peter J. Holzer
ration scripts but of course that assumes that you have such scripts. If you are doing your deployments manually (especially by cloning a template as described by Wilma) I can see how that feature would make things easier and/or reduce the risk of errors. hp -- _ | Peter J. Holzer

Re: Changing a schema's name with function1 calling function2

2023-12-25 Thread Peter J. Holzer
On 2023-12-24 14:27:19 -0800, Adrian Klaver wrote: > On 12/24/23 13:43, Peter J. Holzer wrote: > > I think you misunderstood Wilma. What she is asking for is a "keyword" > > or "magic variable" (or whatever you want to call it) which you can > > specify in

Re: How to do faster DML

2024-02-03 Thread Peter J. Holzer
tructure large enough to hold a count for each individual id. But at least then you'll have a much smaller table to use for further cleanup. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |

Re: How to do faster DML

2024-02-04 Thread Peter J. Holzer
use a chunk size which just fits inside work_mem is faster. Of course finding that sweet spot takes experimentation, hence time, and it may make little sense to experiment for 20 hours just to save 40 minutes. hp -- _ | Peter J. Holzer| Story must make more sense than real

Re: How to do faster DML

2024-02-10 Thread Peter J. Holzer
haracter) suggests that accessing column 100 takes about 4 or 5 times as long as column 1, and the access times for the coiumns between are pretty linear. So there's a bit of a tradeoff between minimizing alignment overhead and arranging columns for fastest access. hp --

Re: How to do faster DML

2024-02-11 Thread Peter J. Holzer
On 2024-02-11 13:25:10 +0530, veem v wrote: > On Sun, 11 Feb 2024 at 05:55, Peter J. Holzer wrote: > Yes. Numbers in Oracle are variable length, so most Oracle tables > wouldn't contain many fixed length columns. In PostgreSQL must numeric > types are fixed lengt

Re: How to do faster DML

2024-02-11 Thread Peter J. Holzer
On 2024-02-11 22:23:58 +0530, veem v wrote: > On Sun, 11 Feb 2024 at 19:02, Peter J. Holzer wrote: > > > Similarly for Number/Numeric data type. > > Number in Oracle and numeric in PostgreSQL are variable length types. > But in PostgreSQL you also have a lot of

Re: How to do faster DML

2024-02-11 Thread Peter J. Holzer
is enough free space in the same page and you can do a HOT update, but that's quite independent on whether the row changes size. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, &qu

Re: How to do faster DML

2024-02-13 Thread Peter J. Holzer
t_postgresql_column_bench where v{i} = 'a'" t0 = time.clock_gettime(time.CLOCK_MONOTONIC) csr.execute(q) r = csr.fetchall() print(r) t1 = time.clock_gettime(time.CLOCK_MONOTONIC) print(i, t1 - t0) db.commit() ---

Re: How to do faster DML

2024-02-13 Thread Peter J. Holzer
On 2024-02-13 01:53:25 +0530, veem v wrote: > On Mon, 12 Feb 2024 at 03:40, Peter J. Holzer wrote: > > The fixed width types are those that the CPU can directly process: > Integers with 16, 32 and 64 bits, floating point numbers with 32 and 64 > bits. The CPU can read

Re: How should we design our tables and indexes

2024-02-13 Thread Peter J. Holzer
x would be useful but doesn't exist, PostgreSQL usually just chooses the best of the single column indexes and ignores the rest. That said, my rule of thumb is to create just single column indexes at first and only create composite indexes if they are necessary. hp -- _ | Pe

Re: How to do faster DML

2024-02-15 Thread Peter J. Holzer
ving himself wrong, of course, but computing correctly is hard - and choosing a data type which more closely mimics the way we learn to compute in primary school doesn't necessarily make it easier. Mostly it just makes it harder to spot the errors ;-). hp -- _ | Peter J. Ho

Re: PostgreSQL DB in prod, test, debug

2024-02-15 Thread Peter J. Holzer
e schema, I run the migration on the test database, then dump and commit it. This project is small enough (86 tests in 10 files) that all test cases can use the same test data. However, I could easily use different test data for different tests. hp -- _ | Peter J. Holzer| Story m

Re: Using a Conversion Table

2024-02-15 Thread Peter J. Holzer
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

Re: How to do faster DML

2024-02-15 Thread Peter J. Holzer
On 2024-02-15 16:51:56 -0700, David G. Johnston wrote: > On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer wrote: > On 2024-02-14 22:55:01 -0700, David G. Johnston wrote: > > On Tuesday, February 13, 2024, veem v wrote: > > > >     float data types rather

Re: How to do faster DML

2024-02-16 Thread Peter J. Holzer
On 2024-02-16 12:10:20 +0530, veem v wrote: > > On Fri, 16 Feb 2024 at 06:04, Peter J. Holzer wrote: > > On 2024-02-15 16:51:56 -0700, David G. Johnston wrote: > > On Thu, Feb 15, 2024 at 4:31 PM Peter J. Holzer > wrote: > >     On 2024-02-14 22:55:

Re: How to do faster DML

2024-02-17 Thread Peter J. Holzer
On 2024-02-16 01:34:01 +0100, Peter J. Holzer wrote: > On 2024-02-15 16:51:56 -0700, David G. Johnston wrote: > > What you see with an exact type is what you get, which allows for > > implementing > > equality, unlike inexact which requires epsilon checking. > > You

Re: Postgres 16 missing from apt repo?

2024-02-24 Thread Peter J. Holzer
ave cached an obsolete index. Use "apt update" to update the index. 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

Re: Orphan files filling root partition after crash

2024-03-03 Thread Peter J. Holzer
hich just terminates all database connections - a bit drastic but effective) if free space runs low: https://github.com/hjp/platzangst hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles St

Re: Thoughts on user-defined types for talk at Postgres conference?

2024-03-07 Thread Peter J. Holzer
s are [...] > the type information (typmod if there is one and the OID of the > composite type), Is it necessary to store this in every row? Can a column contain different composite types? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) |

Re: creating a subset DB efficiently ?

2024-03-09 Thread Peter J. Holzer
scan which may take a long time. 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

Re: Postgresql docker health check

2024-03-14 Thread Peter J. Holzer
u have so many connections. If you have way more connections than you can reasonably expect, something is wrong, And it is better to fix the root cause than to just hit everything over the head with a hammer periodically. hp -- _ | Peter J. Holzer| Story must make more sense than

Re: PostgreSQL as advanced job queuing system

2024-03-22 Thread Peter J. Holzer
"jobs_pkey" PRIMARY KEY, btree (id) "jobs_queue_id_id_idx" btree (queue_id, id) "jobs_queue_id_idx" btree (queue_id) Foreign-key constraints: "jobs_queue_id_fkey" FOREIGN KEY (queue_id) REFERENCES queues(id) If you do have very few very long queues it might be faster to query each queue separately. 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

Re: Is this a buggy behavior?

2024-03-24 Thread Peter J. Holzer
not without searching the table, so that is done first. Only then you have to check the index for a possible duplicate value, so that's done later. But as a user I actually prefer it that way. The more precisely the database can tell me why the insert failed, the better. hp --

Re: Is this a buggy behavior?

2024-03-24 Thread Peter J. Holzer
On 2024-03-24 11:23:22 -0700, David G. Johnston wrote: > On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer wrote: > It doesn't. Your statement > > > CREATE TABLE test1 > > ( > > c1 numeric   NULL , > > c2 varchar(36)  NOT NULL , >

Re: how to check if the license is expired.

2024-03-31 Thread Peter J. Holzer
of the solution. So you ask how to achieve Y. However, Z would be better than Y for solving X, but nobody can tell you because they don't know about X. -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at

expected authentication request from server, but received H

2024-05-23 Thread Peter J. Holzer
yte1('H') could mark a Copy Out response or a Flush command. Both don't make sense in that context. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, &q

Re: expected authentication request from server, but received H

2024-05-27 Thread Peter J. Holzer
On 2024-05-23 17:23:14 -0400, Tom Lane wrote: > "Peter J. Holzer" writes: > > One of our users reports getting the error message > > "expected authentication request from server, but received H" > > when trying to connect to the database. > > That

Re: Multiple tables row insertions from single psql input file

2024-06-10 Thread Peter J. Holzer
ave a value to insert into the foreign key field(s). There is no need to enter all companies before all locations. Indeed, currval() can only (as the name implies) return the *current* value of a sequence, so you can only use it to refer to the last entry you created. If you create two companie

Re: Restore of a reference database kills the auto analyze processing.

2024-06-18 Thread Peter J. Holzer
ting definition of "OPEN". 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

Re: Accommodating alternative column values

2024-07-03 Thread Peter J. Holzer
the intended contents. Try it with select array[email] from people; If that looks promising, you can use it in an alter table statement (Torsten already posted the solution, but I wanted to expand a bit on how to find it). hp -- _ | Peter J. Holzer| Story must make more se

Re: confused about material view locks please explain

2024-07-05 Thread Peter J. Holzer
commit soon enough. 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

Re: confused about material view locks please explain

2024-07-06 Thread Peter J. Holzer
On 2024-07-06 11:09:23 +0530, Krishnakant Mane wrote: > > On 7/5/24 21:10, Peter J. Holzer wrote: > > If I understand https://github.com/sraoss/pg_ivm correctly, the > > materialized view will be updated within the same transaction. So it's > > just the same as any

Re: [EXTERNAL] Re: SSPI Feature Request

2024-07-10 Thread Peter J. Holzer
ow the complete user/group administration to be outsourced to AD. Only GRANTs to database objects like tables, views or functions would need to be done at each database. 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

Re: can stored procedures with computational sql queries improve API performance?

2024-07-10 Thread Peter J. Holzer
sufficient for that. 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

Re: [EXTERNAL] Re: SSPI Feature Request

2024-07-10 Thread Peter J. Holzer
On 2024-07-10 07:27:29 -0700, Ian Harding wrote: > > > On Wed, Jul 10, 2024 at 7:10 AM Peter J. Holzer wrote: > > On 2024-07-09 03:35:33 +, Buoro, John wrote: > > I've dusted off my C books and coded a solution. > [...] > > When using SSP

Re: Dropping column from big table

2024-07-13 Thread Peter J. Holzer
the (former) content of dropped columns, maybe CLUSTER does, too? 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

Re: Dropping column from big table

2024-07-15 Thread Peter J. Holzer
On 2024-07-15 13:53:25 +0200, Laurenz Albe wrote: > On Sun, 2024-07-14 at 00:05 +0200, Peter J. Holzer wrote: > > On 2024-07-11 10:06:47 +0200, Laurenz Albe wrote: > > > Dropping a column is fast, but doesn't reclaim the space. > > > VACUUM won't block

Re: How does this FK constraint error happen?

2024-07-15 Thread Peter J. Holzer
rel_group_user". > ERROR: deleting FISPTAPPGS401DA/TAPd.public.access_user > [snip] Is it possible that some other process created an entry in rel_group_user between these two queries? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_

Re: Dropping column from big table

2024-07-15 Thread Peter J. Holzer
On 2024-07-16 02:00:27 +0530, sud wrote: > > On Mon, Jul 15, 2024 at 7:58 PM Peter J. Holzer wrote: > > Hm, true. > > > > You can always do > > > >   UPDATE tab SET id = id; > > > > followed by > > >

Re: Windows installation problem at post-install step

2024-08-06 Thread Peter J. Holzer
Program Files\PostgreSQL\15\data" > --locale > "Turkish,Türkiye" -W > XXX debug raw: getopt optarg  = "Turkish,Türkiye" > XXX debug hex: getopt optarg  = { 54 75 72 6b 69 73 68 2c 54 fc 72 6b 69 > 79 > 65 } > XXX debug txt: getopt

Re: Column type modification in big tables

2024-08-13 Thread Peter J. Holzer
need to be postgres or root to do this. Be careful! Watching the access times may be useful, too, but on Linux by default the access time is only updated under some special circumstances, so this may be misleading. hp -- _ | Peter J. Holzer| S

Re: How to validate restore of backup?

2024-08-22 Thread Peter J. Holzer
backup of a database to a > NEW server.   > > Is there a way to ensure the data integrity is in tact, and user ID and > access works liked how it was in the old server? And of course your method doesn't check at all whether "user ID and access works liked how i

Re: How to validate restore of backup?

2024-08-23 Thread Peter J. Holzer
On 2024-08-23 08:13:40 +0200, Peter J. Holzer wrote: > On 2024-08-22 16:09:47 +0500, Muhammad Usman Khan wrote: > > For validation of databases, you can use the following approach > > > > /usr/pgsql-16/bin/pg_dump -d postgres -h localhost -p 5428 | md5sum > > &g

Upgrade Ubuntu 22 -> 24 may break PostgreSQL

2024-08-31 Thread Peter J. Holzer
make sure you have a backup before the upgrade. 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

Re: Upgrade Ubuntu 22 -> 24 may break PostgreSQL

2024-08-31 Thread Peter J. Holzer
On 2024-08-31 10:35:01 -0700, Adrian Klaver wrote: > On 8/31/24 09:54, Peter J. Holzer wrote: > > 'Tis the season again. > > > > Ubuntu 24.04.1 has just been released, so many Ubuntu LTS users will now > > be prompted to upgrade from 22.04 to 24.04. > > Wh

Re: Faster data load

2024-09-08 Thread Peter J. Holzer
T was more than twice as fast as 8 parallel COPY operations (and about 8 times as fast as a single COPY). Details will have changed since then (I should rerun that benchmark on a current system), but I'd be surprised if COPY became that much faster relative to INSERT ... SELECT. hp -- _ |

Re: Database schema for "custom fields"

2024-09-10 Thread Peter J. Holzer
now and would probably lean more to your option 1 (let the application add columns to an "extension table"). 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

Re: Manual query vs trigger during data load

2024-09-14 Thread Peter J. Holzer
as ( select substB from cfgB where keyB = :param4 ) insert into target(val1, val2, val3, val4) select :param1, cA.substA, :param3, cB.substB from cA, cB However, I agree with Rob here. It's probably better to do the substitution in Java. hp -- _ | Peter J. Holzer

Re: update faster way

2024-09-14 Thread Peter J. Holzer
ave any method exists > in > postgres (say like forall statement in Oracle) which will do the batch dml. > Can > you please guide me here, how we can do it in postgres. Postgres offers several server side languages. As an Oracle admin you will probably find

Re: Manual query vs trigger during data load

2024-09-15 Thread Peter J. Holzer
On 2024-09-14 21:21:45 +0530, yudhi s wrote: > On Sat, Sep 14, 2024 at 4:17 PM Peter J. Holzer wrote: > On 2024-09-14 00:54:49 +0530, yudhi s wrote: > > As "thiemo" mentioned , it can be done as below method, but if > > we have multiple lookup tables

Re: update faster way

2024-09-15 Thread Peter J. Holzer
On 2024-09-14 20:26:32 +0530, yudhi s wrote: > > > On Sat, Sep 14, 2024 at 4:55 PM Peter J. Holzer wrote: > > > Which in turn means that you want as little overhead as possible per > batch which means finding those 5000 rows should be quick. Which brings &g

Re: Postgres Analog of Oracle APPEND hint

2021-03-05 Thread Peter J. Holzer
s. If you are doing "complicated joins on source tables" that's probably where the bottleneck will be, so you shouldn't worry about the insert speed unless (or until) you notice that the bottleneck is writing the data, not reading it. hp -- _ | Peter J. Holzer

Re: = t1 - t0 but t0 + i <> t1 when t1 and t2 timestamptz values and i is an interval value

2021-04-03 Thread Peter J. Holzer
time”. > > Which are for practical purposes one and the same, otherwise we would not > have leap seconds as a method of syncing the two. I disagree. We have leap seconds exactly because they are not the same. Atomic clock time just counts at at a constant rate - it doesn't care about t

Re: Upgrading from 11 to 13

2021-04-03 Thread Peter J. Holzer
On 2021-04-01 21:56:17 -0400, Bruce Momjian wrote: > On Thu, Apr 1, 2021 at 09:55:28PM -0400, Bruce Momjian wrote: > > Here it is with descriptions: > > Sorry, please ignore. Too late. Now we all know the code names for previous PostgreSQL releases. hp -- _ |

Re: Using indexes through a plpgsql procedure

2021-04-07 Thread Peter J. Holzer
can get away by renaming the old and new table: begin; create table ep_new(...); -- populate ep_new here drop table if exists ep_old; alter table ep rename to ep_old; alter table ep_new rename to ep; commit; Partitioning should also work but that feels like a hack. hp

Re: PostgreSQL log query's result size

2021-04-07 Thread Peter J. Holzer
finding queries which returned 0 rows than those that returned many rows. And for "size of the result data" I think the number of rows would generally be more useful than the size in bytes. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) |

Re: Primary keys and composite unique keys(basic question)

2021-04-08 Thread Peter J. Holzer
). With PostgreSQL I've stopped doing this since the SERIAL type makes it much more convenient to have a separate sequence per table. But of course that means that almost any table will have a row with id 10785 and one with 10875. hp -- _ | Peter J. Holzer| Story must ma

Re: archive_commnad parameter question

2021-04-19 Thread Peter J. Holzer
f" in particular tests whether the argument exists and is a regular file) and the "!" inverts the result. So the whole line checks that the target *doesn't* already exist before attempting to copy over it. hp -- _ | Peter J. Holzer| Story must m

Re: BRIN index on timestamptz

2021-04-26 Thread Peter J. Holzer
your table contains say records from the last year and records are normally only updated after one or two days after being created that would probably still work quite well. If there is a substantial number of records which is still updated after a year, it probably won't work at all.

Re: Invalid byte sequence when importing Oracle BLOB

2021-04-26 Thread Peter J. Holzer
tgresql bytea column. > > Seven times out of about 60M rows, I get this error: > Psql:909242: ERROR:  invalid byte sequence for encoding "UTF8": 0xed 0xaf 0xbf Decoding UTF8 doesn't make sense for a bytea column. How does that data look like in the file generated by ora2

Re: Invalid byte sequence when importing Oracle BLOB

2021-04-26 Thread Peter J. Holzer
On 2021-04-26 07:45:26 -0500, Ron wrote: > On 4/26/21 7:32 AM, Peter J. Holzer wrote: > > On 2021-04-26 06:49:18 -0500, Ron wrote: > > > The destination is an (RDS) Postgresql 12.5 with encoding UTF8, and is > > > being > > > loaded through COPY commands g

Re: PostgreSQL, Asynchronous I/O, Buffered I/O and why did fsync-gate not affect Oracle or MySQL?

2021-05-02 Thread Peter J. Holzer
all four combinations (buffered synchronous, buffered asynchronous, direct synchronous, direct asynchronous) are possible, but some OS's may not implement all of them. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) |

Re: Is this the future of I/O for the RDBMS?

2021-05-02 Thread Peter J. Holzer
may be worthwhile even if only a few systems (Linux systems where the DBA is willing to set up devices for direct access from user space) benefit from it. If it means rewriting large parts of postgres and then some platforms cannot be supported at all or only at reduced performance, this is not a

Re: idle_in_transaction_session_timeout

2021-05-08 Thread Peter J. Holzer
On 2021-05-08 15:58:27 +0530, Atul Kumar wrote: > ok, But what is the workaround of this parameter in postgres 9.5, ,I > need to increase the time of "idle in transaction" transactions. What makes you think that there is an "idle in transaction" timeout in 9.5?

Re: Question about integer out of range in function

2021-05-21 Thread Peter J. Holzer
d. 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

Re: The contents of the pg_timezone_names view bring some surprises

2021-05-22 Thread Peter J. Holzer
time zone "CET" probably reflects what most countries in that zone do, so it is currently also in DST. Same for Eastern European Time. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at

Re: The contents of the pg_timezone_names view bring some surprises

2021-05-22 Thread Peter J. Holzer
rent time zones may use the same abbreviation. But that isn't what the paragraph is about. > What it is saying that, for example, the timezone America/Los_Angeles has > two timezone abbreviations PDT(what I'm currently in) and PST. If you use an > ab

Re: Fwd: Proposed Chinese Translation of Community Code of Conduct

2021-05-22 Thread Peter J. Holzer
oesn't show them. 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

Re: The contents of the pg_timezone_names view bring some surprises

2021-05-22 Thread Peter J. Holzer
On 2021-05-22 12:09:23 +0200, Peter J. Holzer wrote: > On 2021-05-19 06:57:13 -0700, Adrian Klaver wrote: > > On 5/18/21 11:31 PM, Bryn Llewellyn wrote: > > > This seems to be at odds with what section “8.5.3. Time Zones” at > > > > > > https://www.

Re: pgbackrest - hiding the encryption password

2021-05-22 Thread Peter J. Holzer
On 2021-05-19 12:49:42 -0500, Ron wrote: > Currently on our RHEL 7.8 system, /etc/pgbackrest.conf is root:root and 633 > perms. Did you mean 644? 633 would be very strange permissions (write and execute but not read for group and others). hp -- _ | Peter J. Holzer| Stor

Re: The contents of the pg_timezone_names view bring some surprises

2021-05-22 Thread Peter J. Holzer
On 2021-05-22 08:26:27 -0700, Adrian Klaver wrote: > On 5/22/21 3:09 AM, Peter J. Holzer wrote: > > On 2021-05-19 06:57:13 -0700, Adrian Klaver wrote: > > > On 5/18/21 11:31 PM, Bryn Llewellyn wrote: > > > > This claims (as I read it) that a time zone abbreviation

Re: The contents of the pg_timezone_names view bring some surprises

2021-05-24 Thread Peter J. Holzer
case you now have a wrong timestamp in your database which you may or may not be able to catch via other QA measures. 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

Re: index unique

2021-06-06 Thread Peter J. Holzer
doesn work: I find that if a natural primary key candidate is so complex, it is usually better to use a surrogate key. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "

Re: index unique

2021-06-07 Thread Peter J. Holzer
On 2021-06-07 10:20:22 -0700, David G. Johnston wrote: > On Sun, Jun 6, 2021 at 11:55 PM Peter J. Holzer wrote: > On 2021-06-03 22:51:55 +0200, Marc Millas wrote: > > postgres 12 with postgis. > > on a table we need a primary key and to get a unique combinaison, we

Re: index unique

2021-06-08 Thread Peter J. Holzer
2720 exceeds maximum 2712 for index "t_pkey1" HINT: Values larger than 1/3 of a buffer page cannot be indexed. Consider a function index of an MD5 hash of the value, or use full text indexing. Time: 58.751 ms Note the difference between the length of the string I was trying to ins

Re: How to pass a parameter in a query to postgreSQL 12

2021-06-10 Thread Peter J. Holzer
ly that it supports the parameter binding of the Python PostgreSQL library in use (most likely psycopg2). That would be %s for unnamed parameters and %(name)s for named parameters. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) |

Re: bottom / top posting

2021-06-10 Thread Peter J. Holzer
of my time (but if somebody else pays ...) and it is inefficient, as it is very easy to overlook relevant details in that ever-growing mess. I never understood why so many people hated e-mail as a communication medium. Now I do. hp -- _ | Peter J. Holzer| Story must m

Re: bottom / top posting

2021-06-10 Thread Peter J. Holzer
RSE! In business you need the CYA of having the entire discussion > archived, You can archive more than one e-mail per thread, you know :-) hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- C

<    1   2   3   4   5   6   7   >