Re: Fresh eyeballs needed: input into error

2022-04-27 Thread Laurenz Albe
lname,fname,job_title,company_nbr,loc_nbr, loc_phone_ext,direct_phone,direct_fax,cell_phone,email,active,comment) values (6000,'No','Name',null,404,1,null,null,null,null,null,null,null); ERROR: syntax error at or near "insert" LINE 2: insert into people (person_nbr,lname,fname,job_title,company... ^ Yours, Laurenz Albe

Re: errcodes.h and others not generated when compiling 13.6 on M1 Mac

2022-04-27 Thread Laurenz Albe
to happen on > any M1 mac > (my colleagues can reproduce it) > > Any help on how to fix or debug this further would be appreciated. I'd say that you didn't ./configure the PostgreSQL source. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: pg_dump: VACUUM and REINDEXING

2022-05-08 Thread Laurenz Albe
only scans. But from PostgreSQL v13 on, autovacuum is also triggered by INSERTs. So I'd say that there is nothing to do after restoring a pg_dump, except to wait until autovacuum is done. Yours, Laurenz Albe

Re: Deferred constraint trigger semantics

2022-05-10 Thread Laurenz Albe
onal locking or SERIALIZABLE isolation to make that work reliably. That does not mean that using constraint triggers is unsound or unsupported, and the fact that Oracle's implementation of transaction isolation is somewhat shoddy has little impact on that. Yours, Laurenz Albe

Re: Deferred constraint trigger semantics

2022-05-11 Thread Laurenz Albe
after COMMIT has been issued. (I *am* > able to do this > to expose the fact that "set constraints all immediate" is unsafe.) This sentence lacks the definition of what you mean by "safe", on which all hinges. If "safe" means that you can use them to make sure that a certain condition is always satisfied (like in a constraint), they are not safe. But that is not the only use for a trigger. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Deferred constraint trigger semantics

2022-05-12 Thread Laurenz Albe
the job is "Admin". So, here, concurrent sessions can break the rule (when > the txn starts > with one "Admin") by updating different rows to make them "Admin" or by > inserting different > new "Admin" rows. I've convinced myself by experiment that an ordinary > trigger can enforce > this rule when contending sessions use "serializable" isolation. Am I right > that you'd say > that no pessimistic locking scheme can enforce the rule at lower isolation > levels except the > brute-force "lock table"? If the transaction can insert two rows, I'd agree. If the transaction only inserts a single row, than it could SELECT ... FOR NO KEY UPDATE the one existing row, thus serializing the concurrent transactions. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: About pg_basebackup

2022-05-18 Thread Laurenz Albe
ts to be > archived (120 seconds elapsed) I guess that your "archive_command" is hanging or failing. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: In case of network issues, how long before archive_command does retries

2022-05-19 Thread Laurenz Albe
; > For detail, I'm using postgres 11, running on Ubuntu 20. You can find the details in "src/backend/postmaster/pgarch.c". The archiver will try to archive three times (NUM_ARCHIVE_RETRIES) in an interval of one second, then back off until it receives a signal, PostgreSQL shutd down or a minute has passed. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: In case of network issues, how long before archive_command does retries

2022-05-19 Thread Laurenz Albe
On Thu, 2022-05-19 at 15:43 +0200, Koen De Groote wrote: > On Thu, May 19, 2022 at 9:10 AM Laurenz Albe wrote: > > On Wed, 2022-05-18 at 22:51 +0200, Koen De Groote wrote: > > > When connection is gone or blocked, archive_command fails after the > > > timeout specified

Re: Long living and expiring locks?

2022-05-20 Thread Laurenz Albe
on't have to keep a transaction open. (Regular) long transactions are a big problem in relational databases. In PostgreSQL you could use the system columns "ctid" and "xmin" to check if the row changed, so you don't have to fetch and check all columns. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: existing row not found by SELECT ... WHERE CTID = ?

2022-05-24 Thread Laurenz Albe
ransactoin that creates the cursor commits, so the result set is "frozen" and does no longer reflect the current state of the table. It may well be that somebody deleted or updated a few rows between the time the cursor was materialized and the time the 5th row was fetched. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: existing row not found by SELECT ... WHERE CTID = ?

2022-05-25 Thread Laurenz Albe
ery. Yours, Laurenz Albe

Re: existing row not found by SELECT ... WHERE CTID = ?

2022-05-25 Thread Laurenz Albe
sion has modified the row, you will get a serialization error. So that is not a solution. Yours, Laurenz Albe

Re: Connect to specific cluster on command line

2022-05-25 Thread Laurenz Albe
orresponding Unix domain socket directory via the --host option? With -h you specify the directory containing the socket, and with -p (port) the name. So you could psql -h /var/run/postgresql -p to use the socket /var/run/postgresql/.s.PGSQL.5555 Yours, Laurenz Albe -- Cybertec | htt

Re: autovacuum on primary blocking queries on replica?

2022-05-29 Thread Laurenz Albe
lict with any query on that table on the standby. You can disable autovacuum truncation on the table with ALTER TABLE foo_all.public.industry SET (vacuum_truncate = off); if you know that you can do without autovacuum truncation for that query. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: How to display complicated Chinese character: Biang.

2022-06-02 Thread Laurenz Albe
graphemes should be 1? You have an extra "0" there; "\+" unicode escapes have exactly 6 digits: WITH strings(s) AS ( VALUES (U&'\+030EDD') ) select s, octet_length(s), char_length(s) from strings; s │ octet_length │ char_length ╪

Re: Automatic autovacuum to prevent wraparound - PG13.5

2022-06-15 Thread Laurenz Albe
esponsible > for this behaviour? A long-running transaction or a prepared transaction. Or an abandoned replication slot with an old "xmin". That would be the answer for PostgreSQL. It might apply to Amazon Aurora, unless they changed the behavior there. Perhaps ask Amazon. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Postgresql error : PANIC: could not locate a valid checkpoint record

2022-06-17 Thread Laurenz Albe
point 13.7. Anyway, the error message looks like somebody removed the contents of the "pg_wal" directory. How exactly did you upgrade PostgreSQL? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: accessing postgres from c++

2022-06-20 Thread Laurenz Albe
ne can help. thanks. I am surprised that you choose to be fettered by the constraints of a generic API like ODBC. For me, that only makes sense if you want to be portable to different databases. I would use the powerful C API of libpq, or, if you want some C++ boilerplate around it, us

Re: Question about attention to pgsql-hack...@lists.postgresql.org

2022-06-26 Thread Laurenz Albe
self or hire someone to do it for you (but then you should come up with a better specification). Yours, Laurenz Albe

Re: Table space not returned to the OS ?

2022-06-27 Thread Laurenz Albe
grade. Once you have started the PostgreSQL 14 server (to verify that it works), you can no longer use the old cluster. Yes, the control file is crippled, but in my opinion, the earlier you delete the old cluster, the safer. Yours, Laurenz Albe

Re: help for pg_wal issue

2022-06-27 Thread Laurenz Albe
001000A00B5 > -rw--- 1 postgres postgres 16777216 Jun 24 01:31 0001000A00B6 > -rw--- 1 postgres postgres 16777216 Jun 24 01:46 0001000A00B7 > -rw--- 1 postgres postgres 16777216 Jun 24 02:01 0001000A00B8 You forgot to tell us how exactly you are performing that backup. Yours, Laurenz Albe

Re: User's responsibility when using a chain of "immutable" functions?

2022-06-28 Thread Laurenz Albe
REATE OR REPLACE FUNCTION const(integer) RETURNS integer LANGUAGE plpgsql IMMUTABLE AS 'BEGIN RETURN $1 + 1; END;'; SELECT * FROM t WHERE const(x) = 1; -- returns a bad result x ═══ 1 (1 row) Of course, you are allowed to cheat if you know what you are doing. But any problem you enc

Re: Fatel: unsupported frientend protocol error

2022-06-30 Thread Laurenz Albe
server supports 1.0 to 3.0 > > This error continuesly getting in log file This is simple: upgrade the server to a supported version, ideally v14. Yours, Laurenz Albe

Re: lifetime of the old CTID

2022-07-05 Thread Laurenz Albe
t; > Why is this? And what triggers exactly that the old CTID can't be used > anymore? It is probably the fault of your coffee. Another explanation could be that the HOT chain was pruned while you were away. Yours, Laurenz Albe

Re: lifetime of the old CTID

2022-07-05 Thread Laurenz Albe
On Tue, 2022-07-05 at 12:22 +0200, Matthias Apitz wrote: > El día Dienstag, Juli 05, 2022 a las 10:40:40 +0200, Laurenz Albe escribió: > > On Tue, 2022-07-05 at 09:51 +0200, Matthias Apitz wrote: > > > We're using the SQL function currtid2() to get the new CTID of a row >

Re: lifetime of the old CTID

2022-07-05 Thread Laurenz Albe
On Tue, 2022-07-05 at 17:52 +0200, Matthias Apitz wrote: > El día martes, julio 05, 2022 a las 04:17:41p. m. +0200, Laurenz Albe > escribió: > > > > > Another explanation could be that the HOT chain was pruned while you > > > > were away. > > > > &g

Re: lifetime of the old CTID

2022-07-05 Thread Laurenz Albe
FOR b IN SELECT pk, other_columns FROM books WHERE condition UPDATE books SET ... WHERE pk = ... AND condition Checking the condition again on the inner UPDATE will detect concurrent data modifications. If the UPDATE changes nothing, then a book has been removed or updated by a concurrent transaction, and you ignore it. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: How to upgrade postgres version 8 to 13

2022-07-06 Thread Laurenz Albe
Yours, Laurenz Albe

Re: About revoking large number of privileges; And the PUBLIC role.

2022-07-07 Thread Laurenz Albe
faster than DROP OWNED BY. > That DB took 30min to 1h to get rid of, via DROP OWNED BY, which seems > to long to delete a bunch of files, no? As I wrote, avoid getting there in the first place. Yours, Laurenz Albe

Re: postgresql bug

2022-07-08 Thread Laurenz Albe
> What should I do? Either install the older version if the ICU library, or build PostgreSQL against the newer version. Yours, Laurenz Albe

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

2022-07-12 Thread Laurenz Albe
out password is not possible. The easiest solution is to change the "pg_hba.conf" entries for "localhost" in IPv4 and IPv6 to "trust" for the duration of the upgrade. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Problem upgrading from 10 to 14 with pg_upgrade: unexpected error upgrading "template1" database for some clusters

2022-07-12 Thread Laurenz Albe
On Wed, 2022-07-13 at 02:53 +, Ken Yeung wrote: > From: Laurenz Albe > Sent: Wednesday, July 13, 2022 3:16 AM > > > You configured "pg_hba.conf" so that a local login without password is > > not possible.  The easiest solution is to change the "pg_hba.

Re: i added Arabic Dictionary but how I know i'm using it

2022-08-17 Thread Laurenz Albe
ql.org/docs/current/textsearch-dictionaries.html#TEXTSEARCH-SYNONYM-DICTIONARY An Ispell dictionary normalizes words, for example by removing suffixes for plural and case. You'd have to create a synonym file yourself. Yours, Laurenz Albe

Re: Is it possible to keep indexes on different disk location?

2022-08-18 Thread Laurenz Albe
sk? (DB > recoverable instead of total crash)? I'd say that that is a bad idea. It would not be easy to recover from losing a tablespace, even if it contains only indexes. Get a real computer. Take backups regularly. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: recovery_command has precedence over phisical slots?

2022-08-19 Thread Laurenz Albe
connection to the primary as defined in "primary_conninfo" and stream WAL from there. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: recovery_command has precedence over phisical slots?

2022-08-24 Thread Laurenz Albe
On Wed, 2022-08-24 at 14:18 +0900, Kyotaro Horiguchi wrote: > At Fri, 19 Aug 2022 18:37:53 +0200, Laurenz Albe > wrote in > > On Fri, 2022-08-19 at 16:54 +0200, Giovanni Biscontini wrote: > > > Hello everyone,  > > > I'm experiencing a behaviou

Re: CVE-2022-2625

2022-09-14 Thread Laurenz Albe
ould certainly not be running a PostgreSQL version that is out of support. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Re[2]: CVE-2022-2625

2022-09-15 Thread Laurenz Albe
On Thu, 2022-09-15 at 07:24 +0300, misha1966 misha1966 wrote: > > Четверг, 15 сентября 2022, 1:58 +09:00 от Laurenz Albe > > : > >   > > On Wed, 2022-09-14 at 17:02 +0300, misha1966 misha1966 wrote: > > > Tell me, is there a CVE-2022-2625 vulnerability in posgresq

Re: Re[2]: CVE-2022-2625

2022-09-15 Thread Laurenz Albe
hat say "CVE". Never mind that username = password and the application is running with a superuser. Yours, Laurenz Albe

Re: Re[4]: CVE-2022-2625

2022-09-18 Thread Laurenz Albe
On Mon, 2022-09-19 at 07:35 +0300, misha1966 misha1966 wrote: > > Четверг, 15 сентября 2022, 17:22 +09:00 от Laurenz Albe > > : > >   > > On Thu, 2022-09-15 at 07:24 +0300, misha1966 misha1966 wrote: > > > > Четверг, 15 сентября 2022, 1:58 +09:00 от Laurenz

Re: Suggest using boolean index with (bflag is true) condition for the query with (bflag = true) clause

2022-09-19 Thread Laurenz Albe
Because the conditions are different: SELECT NULL = TRUE, NULL IS TRUE; ?column? │ ?column? ══╪══════ │ f (1 row) The first result is NULL. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: PostgreSQL Rule does not work with deferred constraint.

2022-09-21 Thread Laurenz Albe
quot;children" values (default); > The rules system supposedly transforms the insert statement and executed the > rule action in the same transaction. So I think it should work.  > But I got the same error on both pg13 and pg14. > > Is there something I missed here? or is my understanding of the rule system > just simply wrong?  I just tried your commands, and it works as you expect on my PostgreSQL v15 database. Yours, Laurenz Albe

Re: pg_stat_activity.backend_xmin

2022-09-21 Thread Laurenz Albe
whole duration of a transaction in the REPEATABLE READ isolation level, but there are cases where you can see that in READ COMMITTED isolation level as well: - if there is a long running query - if there is a cursor open Perhaps you could ask your developers if they have long running read-only t

Re: PCI-DSS Requirements

2022-09-22 Thread Laurenz Albe
e sensitive data, so that you can retain some degree of usability. A great deal will also rest on your database user management and authentication, and how well access to the system is logged and controlled. All that said, there is always a difference between good security and passing a certification exam... Yours, Laurenz Albe

Re: ECCN for PostgreSQL

2022-09-22 Thread Laurenz Albe
never exported at all. I don't see how it can be subject to any regulation on export. Yours, Laurenz Albe

Re: [EXT] pg_stat_activity.backend_xmin

2022-09-22 Thread Laurenz Albe
saction > > > isolation level to > > > repeatable read.  We have enabled statement logging so we can see if > > > their sessions are > > > changing that transaction isolation level behind the scenes that they are > > > not aware of > > > but so far we have not seen that type of command logged. > > > > What stack is the application using?  Anything like Spring or Hibernate > > involved? > > Java is the stack. I'm not saying that you shouldn't trust your developers, but they may be using a cursor without being aware of it. If they use "setFetchSize()" to set a fetch size different from 0, they *are* using a cursor. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Database Horizon

2022-09-22 Thread Laurenz Albe
ive. The transaction ID sets the "xmin horizon" in such a case. For a reading transaction, it is the xmin horizon of the current snapshot that holds back VACUUM. For a READ COMMITTED transaction, there is only a snapshot for running statements and open cursors. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Support functions for range types

2022-09-26 Thread Laurenz Albe
it only deals with constant operands. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Findout long unused tables in database

2022-09-26 Thread Laurenz Albe
gging all statements. I expect that removing permissions on a table and checking whether your application hits an error is not an option... Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Findout long unused tables in database

2022-09-27 Thread Laurenz Albe
st solution. I should have thought of that. Yours, Laurenz Albe

Re: Postgresql database and application server compatibility

2022-09-30 Thread Laurenz Albe
ing. Make sure to upgrade the PostgreSQL client library on the application server as well. The more recent that is, the better. Test well. Yours, Laurenz Albe

Re: Number of updated rows with LibPQ

2022-10-05 Thread Laurenz Albe
o use PQcmdTuples(), and you have to convert the string to an integer. But don't worry: the result will *not* be "INSERT 0 5", it will be just "5", so you won't have to parse anything. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Weird planner issue on a standby

2022-10-12 Thread Laurenz Albe
inting to dead table tuples, and it took a long time to find the maximal entry in the table, which is done by the optimizer. VACUUM removed those dead tuples and their associated index entries on both primary and standby. Yours, Laurenz Albe

Re: Number of updated rows with LibPQ

2022-10-14 Thread Laurenz Albe
On Fri, 2022-10-14 at 13:52 +0200, Dominique Devienne wrote: > On Wed, Oct 5, 2022 at 8:17 PM Tom Lane wrote: > > Laurenz Albe writes: > > > On Wed, 2022-10-05 at 16:38 +0200, Dominique Devienne wrote: > > > Yes, you have to use PQcmdTuples(), and you have to conv

Re: [libpq] OIDs of extension types? Of custom types?

2022-10-14 Thread Laurenz Albe
built-in Oids, right? For types from an extensions, you would run a query on "pg_type". Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: does postgres has snapshot standby feature?

2022-10-14 Thread Laurenz Albe
dby. If you removed the file, you did something wrong. You have to promote the standby properly. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: 回复:回复:A question about leakproof

2022-10-17 Thread Laurenz Albe
f, the optimizer will not move it "inside" the view definition. Then the function is evaluated only after the view definition. That may very well lead to a slower execution plan, because it cannot use certain indexes on the underlying tables. It is the price you have to pay for good security. Yours, Laurenz Albe

Re: 回复:回复:回复:A question about leakproof

2022-10-17 Thread Laurenz Albe
xplain how it causes data leakage? I don't know the reason in this case. You could look at the source code, perhaps it is possible to cause error messages that can give you some clue as to the value that you compare with. But perhaps, as Tome said, it is just that nobody scrutinized the f

Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP

2022-10-18 Thread Laurenz Albe
ID -- 42 I looks like Oracle allows you to randomly interfere with its transaction handling. If you run commit and then enter an exception handler, it simply doesn't rollback. Yours, Laurenz Albe

Re: what's inherited

2022-10-18 Thread Laurenz Albe
On Tue, 2022-10-18 at 16:41 -0500, Ted Toth wrote: > When I create a table that inherits from another table what all is > inherited i.e. ownership, security policy, ...? None of that, only the column definitione. Yours, Laurenz Albe

Re: Is this error expected ?

2022-10-19 Thread Laurenz Albe
cumentation explicity says that "char" is not intended for use by the end user (https://www.postgresql.org/docs/current/datatype-character.html): > These are not intended for general-purpose use, only for use in the internal > system catalogs. You will have to add an explicit type cast. Yours, Laurenz Albe

Re: How to store "blobs" efficiently for small and large sizes, with random access

2022-10-19 Thread Laurenz Albe
hould reconsider. Ruling out that option now might get you into trouble later. Large Objects mean trouble. Yours, Laurenz Albe

Re: How to store "blobs" efficiently for small and large sizes, with random access

2022-10-20 Thread Laurenz Albe
actly they are, but I suspect that they are just files (segments?) in Oracle's "file system" (tablespaces/datafiles). So pretty much what we recommend. Yours, Laurenz Albe

Re: Mysterious performance degradation in exceptional cases

2022-10-21 Thread Laurenz Albe
s an ACCESS EXCLUSIVE lock, like TRUNCATE or ALTER TABLE. But there are other possibilities, like network problems or a problem inside your application. To narrow that down, set "log_min_duration_statement" and possibly activate "auto_explain", so that you can verify if the long delay is due to long processing time inside the database. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: PQconsumeinput() may close the fd

2022-10-21 Thread Laurenz Albe
the connections afterwards. Can you find any corresponding messages in the PostgreSQL server log? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: constantly updated table in Amazon RDS

2022-10-21 Thread Laurenz Albe
tics more often by lowering "autovacuum_analyze_scale_factor" for that table: ALTER TABLE tab SET (autovacuum_analyze_scale_factor = 0.01); You'd have to experiment for the best value. Yours, Laurenz Albe

Re: synchronous streaming replication

2022-10-23 Thread Laurenz Albe
acuum_truncate = off"), but you will never get rid of them completely. You will either have to accept stale ready on the standby (by setting "synchronous_commit" to something lower) or you have to accept canceled queries on the standby (by lowering "max_standby_streaming_delay"). Yours, Laurenz Albe

Re: How to know how much CPU, RAM is used by existing 1 database

2022-10-26 Thread Laurenz Albe
er hardware. Thank you You can look how much *private* memory the backends for a certain database use by examining /proc//smaps, you can use the pg_buffercache extension to see how much of which database is in cache, but you don't know how much of the kernel page cache is used to cache files for that database. Yours, Laurenz Albe

Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

2022-10-31 Thread Laurenz Albe
> want to > set up for "local", "peer" authentication into the "postgres" group. > > What do you (all) think? I think that you are doing something very weird, but I have no idea what it is. Please tell us the exact commands you ran. The client user should *

Re: Autovacuum on Partitioned Tables

2022-10-31 Thread Laurenz Albe
> partition relationship?  I agree that this is unclear and have proposed an improvement: https://postgr.es/m/1fd81ddc7710a154834030133c6fea41e55c8efb.ca...@cybertec.at Yours, Laurenz Albe

Re: autovacuum hung on simple tables

2022-11-04 Thread Laurenz Albe
| 0 >  autovacuum_vacuum_cost_limit    | 5000  >  autovacuum_work_mem | -1    >  vacuum_freeze_min_age   | 5000  >  vacuum_freeze_table_age | 15000 > > I'm now thinking that autovacuum getting hung up is what caused the issue to > begin with. I see nothing > but the successful vacuums from the script and my own fat-fingering commands > in the postgres > logs (set at info). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Some questions about Postgres

2022-11-04 Thread Laurenz Albe
.com/blog/2016-07-29/on-ubers-choice-of-databases Yours, Laurenz Albe

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

2022-11-07 Thread Laurenz Albe
uld make troubleshooting not only much faster but > non-invasive for the other databases on the same server as well. Crashing is never nice. On the other hand, adding checks and error messages for conditions that are always true in a correct block cost performance. I can't tell about your specific case, but a build of PostgreSQL --enable-cassert has assitional checks in the code. That will still crash, but the log will show what condition was violated. Yours, Laurenz Albe

Re: postgres replication without pg_basebackup? postgres 13.3

2022-11-07 Thread Laurenz Albe
rimary which is already in place.  Is it possible > without using pg_basebackup? That's exactly what "pg_rewind" is for. It is a fast version of "pg_basebackup" for exactly that case. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: PCI:SSF - Safe SQL Query & operators filter

2022-11-07 Thread Laurenz Albe
ion user has restricted privileges as it should, it is automatically limited in the damage it can do. Yours, Laurenz Albe

Re: postgres replication without pg_basebackup? postgres 13.3

2022-11-07 Thread Laurenz Albe
07 22:57:55 CET-]LOG:  database system is shut down > > We have read we can run pg_resetwal but the Wal folder is the folder where > the Wals are archived in the primary. Would that be correct to reset them? Don't run "pg_resetwal". At the very least, it will break your standby. At this point, your standby seems to be broken. I don't know what exactly you did, but it leeks like you should run a "pg_basebackup" after all. Yours, Laurenz Albe

Re: PCI:SSF - Safe SQL Query & operators filter

2022-11-07 Thread Laurenz Albe
understand what you want to demonstrate with the code samples, or what you mean when you say that "the user can send an explicit command". Yours, Laurenz Albe

Re: Unnecessary locks for partitioned tables

2022-11-09 Thread Laurenz Albe
Yes, of course. It needs an ACCESS SHARE lock when it looks at metadata like the partition constraint, and locks are held until the end of the transaction. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Table : Bloat grow high

2022-11-12 Thread Laurenz Albe
;t require VACUUM for cleaning up. https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/ Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Table : Bloat grow high

2022-11-13 Thread Laurenz Albe
On Sun, 2022-11-13 at 14:50 +, Alexis Zapata wrote: > I found that xmin does not change when running the vacuum. Which xmin? Yours, Laurenz Albe

Re: An I/O error occured while sending to the backend

2022-11-15 Thread Laurenz Albe
rity.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1065) > at > org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:161) Well, set a longer socket timeout if you need to run long SQL statements, or tune those statements to be faster. https://jdbc.postgresql.org/documentation/use/#connection-parameters has more information about "socketTimeout". Yours, Laurenz Albe

Re: Configure StopWords in full text search without a configuration file?

2022-11-17 Thread Laurenz Albe
or have a user that is a member of "pg_write_server_files", you could write the stopword file via SQL. Other than that, I see no possibility. Yours, Laurenz Albe

Re: Calculating average block write time

2022-11-18 Thread Laurenz Albe
ecause each > tuple change would require at least one block written. > However, a single block write could update multiple tuples so the result of > that wouldn't be accurate (too low). You cannot get that, because most writes are done by the checkpointer, and that does not distinguish

Re: system variable can be edited by all user?

2022-11-22 Thread Laurenz Albe
, and there is no way you can prevent a database user from changing them for her session. The recommendation is not to give untrustworthy users access to directly run SQL statements on the database. Yours, Laurenz Albe

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

2022-11-23 Thread Laurenz Albe
ueries, that would be most useful. Yours, Laurenz Albe

Re: Index filter instead of index condition w/ IN / ANY queries above certain set size

2022-11-23 Thread Laurenz Albe
            I/O Timings: read=2369932.536 PostgreSQL thinks that there are enough such rows that it is cheaper to use the index that supports the ORDER BY. I don't know why there is a difference between = ANY and = here, but you can use an expression like "ORDER BY pidh + 0" to avoid that. Yours, Laurenz Albe

Re: Getting PSQL in Windows to support TAB/Autocomplete via modified readline...

2022-11-23 Thread Laurenz Albe
now there's > > an 80% chance that > > this may fail to get accepted for any number of reasons. > > I don't think that estimate needs to be that pessimistic. > > Thanks for the effort to bring tab completion to psql on windows. Yes, that would be a great achievement! Yours, Laurenz Albe

Re: autovacuum hung on simple tables

2022-11-27 Thread Laurenz Albe
ds after it finished, I queried for 'active' in > pg_stat_activity and the oldest relfrozenxid query was still listed. > A few seconds later it had cleared. That sounds weird and is hard to believe. Are the disk or the CPU under extreme stress? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: Replicating an existing (huge) database

2022-11-28 Thread Laurenz Albe
e is the "low level backup API" (pg_start_backup() and pg_stop_backup()) that allows you to copy the data yourself, for example with storage snapshots, which can be much faster. The essential process is like with pg_basebackup, but you can use more efficient methods to copy the data.

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

2022-12-01 Thread Laurenz Albe
robust way to do development. You should use a schema versioning tool like Liquibase, develop schema migration scripts and maintain the SQL code in a source repository like other software. At any rate, you won't be able to do it in your accustomed way in PostgreSQL. Yours, Laurenz Albe

Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

2022-12-02 Thread Laurenz Albe
aps we should improve the acceptance of PostgreSQL by removing those features? Yours, Laurenz Albe

Re: tcp keepalives not sent during long query

2022-12-14 Thread Laurenz Albe
en they run a long query. > > Has anything changed in postgres that might cause this? e.g. that keepalives > are only sent when the session is idle? It is the operating system kernel that sends keepalives, so that should be independent of what the PostgreSQL backend is doing. Yours, Laurenz Albe

Re: tcp keepalives not sent during long query

2022-12-15 Thread Laurenz Albe
; >  tcp_keepalives_idle > - >  0 > (1 row) One good way to debug this is SELECT setting, source, sourcefile, sourceline FROM pg_settings WHERE name = 'tcp_keepalives_idle'; That will tell you from where you get the parameter value. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: dropped default locale

2022-12-21 Thread Laurenz Albe
7;default', 11, 10, 'd', TRUE, -1, NULL,NULL), (950, 'C', 11, 10, 'c', TRUE, -1, 'C', 'C'), (951, 'POSIX', 11, 10, 'c', TRUE, -1, 'POSIX', 'POSIX'); Yours, Laurenz Albe

Re: Regd. the Implementation of Wallet (in Oracle) config equivalent in postgreSQL whilst the database migration

2022-12-21 Thread Laurenz Albe
https://www.postgresql.org/docs/current/auth-cert.html Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: postgres restore & needed history files

2023-01-05 Thread Laurenz Albe
> Question: Is it necessary to retain all history files? Yes, the history files are an integral part of the database. You must not delete them from your WAL archive. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com

Re: best practice to patch a postgresql version?

2023-01-05 Thread Laurenz Albe
: > For minor releases, the community considers not upgrading to be riskier than > upgrading. Of course you will update your test databases first, to make sure that your update procedure is working well. Yours, Laurenz Albe

Re: default icu locale for new databases (PG15)

2023-01-12 Thread Laurenz Albe
or the Åland Islands. I don't speak Swedish, so I don't know how different they are and how they differ from the generic "sv". I don't think any of these Locales will go away in the forseeable future. Yours, Laurenz Albe

Re: AW: [Extern] Re: postgres restore & needed history files

2023-01-13 Thread Laurenz Albe
ackup that was taken on an earlier timeline. Yours, Laurenz Albe

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