r to access a foreign
server.
Create one per user and foreign server (or a single one for PUBLIC =
everybody).
- The foreign table describes how a remote table is mapped locally.
Define one per table that interests you.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
.in pg_hba.conf?
No, in SQL:
CREATE FOREIGN SERVER somename FOREIFN DATA WRAPPER postgres_fdw OPTIONS
(...);
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote:
> you may do this, for example:
>
> (b it not null and b = true) and (c is not null)
>
> Or something like that.
My (equivalent) suggestion:
b IS TRUE AND c IS NOT NULL
Yours,
Laurenz Albe
--
Cybertec | https
ation conflict?
What is in "pg_stat_database_conflicts" on the standby server?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
uently.
You should do exactly what the hint that goes with the message recommends.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
"TRUE" are defined in "umachine.h", which is a header file for the
"libicu" library.
PostgreSQL includes "unicode/ucol.h", which will include "umachine.h"
(via "utypes.h"), so that should be fine.
Are your libicu headers installed under /usr/include/unicode?
Do you get any messages about missing include files earlier?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Mon, 2020-11-16 at 22:17 +1300, Thomas Munro wrote:
> On Mon, Nov 16, 2020 at 10:10 PM Laurenz Albe
> wrote:
>
> > On Mon, 2020-11-16 at 09:15 +0200, Condor wrote:
> > > collationcmds.c: In function ‘get_icu_language_tag’:
> > > collationcmds.c:467:51: error
ago. I find it hard to believe that sprinkling "#include
> " into random places is either necessary (on modern platforms
> anyway) or a good idea (if we're not using , this seems pretty
> much guaranteed to break things); so I think the rest of that patch is
> foolh
R: could not access file
> "$libdir/plpython2": No such file or directory
> In database: argosrm
> In database: template1
The problematic function is perhaps in another database.
Look everywhere.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ems that
they create (long duration of ATTACH/DETACH PARTITION, index fragmentation).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ql/vacuum
>
> Hope you find it useful.
Then I can chime in with
https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/
Yours,
Laurenz Albe
u upgrade, move to v13.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ince 9.2, as far as I can tell.
The underscore in front of the function name is relevant: perhaps you compile
your function
using a different "calling convention" than was used to build PostgreSQL?
Sorry, but I am not a Windows expert.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
; and got to know that the loading nearly stops (without any real CPU
> consumption) in the middle. The wild guess is that we forgot to DROP the
> indexes on
> the tables.
If it does not consume CPU, it must be stalled somehow.
Are there any wait events in "pg_stat_activity&quo
> affected.
There is no way to do that in PostgreSQL.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
source consuming activities on them again.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
-> HashAggregate
Group Key: z_flat.applicant_name
-> Seq Scan on xyz_1 z_flat
[8 more such partition scans]
(33 rows)
How does the optimizer decide to use 4 parallel workers?
No matter what I try, I cannot influence that number.
Yours,
Laurenz Albe
case, I do not see how REPEATABLE READ could differ
> from SERIALIZABLE. Yet [1] explains that:
There is an example in the Wiki:
https://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions
In that example, serializability is broken only because of a READ ONLY
transaction.
Yours,
Laurenz
like this:
>
> INSERT INTO foreign.labels (address, labels)
> VALUES (), (), (), ();
>
> postgres_fdw would send it as individual INSERTs?
Yes, that's the way the FDW API works.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
re details to error message. The client gets less
information,
because such information could be useful to an attacker.
I'd expect that you get at least the line in pg_hba.conf that was used, which
will
ease debugging for you.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
trust
hostreplication all ::1/128 trust
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
rested
> to hear what approach others have taken ?
I have not personally taken that approach, but you could give the
ltree extension a go.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ations:
- The function is executed after the "Gather" node.
Perhaps you didn't define it as PARALLEL SAFE.
- Perhaps the tables are small.
During a parallel sequential scan, each worker is assigned a range of blocks
to scan,
so all rows found in a single block are scanned by a single worker.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Tue, 2020-12-01 at 15:23 +, Zwettler Markus (OIZ) wrote:
> I want to do a Connection FROM Postgres@Linux using fdw + odbc TO
> SQL-Server@Windows.
Is there any reason for not using tds_fdw?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, 2020-11-25 at 17:36 +0100, Laurenz Albe wrote:
> I have a partitioned table, each partition has "parallel_workers = 10" set.
>
> SET max_parallel_workers_per_gather = 8;
>
> SET enable_partitionwise_aggregate = on;
>
> EXPLAIN (COSTS OFF)
>
ore
subtle. For example, if someone asks you why an index isn't used to
support sorting, you'd always have to remember to ask what collation
has been set in the session.
Yours,
Laurenz Albe
= &p.lname AND p.fname = &p.fname;
> -
>
> Suggestions on what I should read to learn more about this subject are
> appreciated.
You probably need the \prompt psql command:
\prompt 'What is "p.lname"' p_lname
\prompt 'What is "p.fname"' p_fname
SELECT ... WHERE p.lname = :p_lname AND p.fname = :p_fname;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
tup".
> This smells strongly of filesystem corruption which requires a Windows guru.
Not that I am one, but this smacks of anti-virus software that mistakenly
thinks "pg_ctl.exe" is malware and removes or "isolates" it.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
e getting rid of old data. That can be quite
painful with a single large table, but it might be trivial with partitioning.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Filter: (((NOT reserved) AND
(task_type = 1)) OR (task_type = 2))
-> Index Scan using
task_child_2_task_timestamp_idx on task_child_2 task_parent_8
Filter: (((NOT reserved) AND
(task_type = 1)) OR (task_type = 2))
(29 rows)
The subquery is executed twice, and the two executions obviously don't
return the same results. I am at a loss for an explanation ...
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
not others. I can’t think of a way to do that offhand.
Where do you see that? As far as I know, VOLATILE is the best choice if you
want the function to be inlined.
I would say that the simplest way to prevent a function from being inlined
is to set a parameter on it:
ALTER FUNCTION f()
pretty(pg_table_s..."
I don't see the problem.
Cast "table_name" and "table_schema" to "text" wherever it occurs.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ql.com/en/subtransactions-and-performance-in-postgresql/
You could export a snapshot with pg_export_snapshot() and see if the resulting
file
contains the line "sof:1".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
of foreign keys, while that statement is removed from
> the docs of Postgres 13. How is a 1:1 relationship guaranteed in newer
> version when PRIMARY KEYS are still local to their individual
> partition table?
There are foreign keys referencing partitioned tables from v12 on.
You can guarantee a 1:1 relationship with a unique constraint on the
source columns.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
work better or should I just be looking at
> scheduling a manual vacuum/analyze
> periodically on all databases?
That is not necessary.
You should figure out what tables need more frequent ANALYZE runs to get good
execution plans
and tune autoanalyze for these tables, for example by reducing
on,
> query,
> state
> FROM pg_stat_activity
> WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
Use xact_start rather than query_start.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
>
> Should _PG_init(void) be declared in someplace included by postgres.h or
> fmgr.h?
It is provided by your module, so it must be declared by your module:
extern void _PG_init(void);
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
(attached logs) messages, I am missing some libraries,
> what should I install?
The logs complain about missing the "dblink" library, so you forgot to install
the -contrib package.
Remember to REINDEX all indexes on string columns, because the collations
may have changed.
Yours,
Laur
be
preserved.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
files in the data directory.
What you can do is move the *complete* data directory to another location
with more disk space and start it there.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
mycompany.com/dc=mycompany,dc=com?description?one?(cn=${PGDATABASE})
I proposed something like that a while ago:
https://postgr.es/m/D960CB61B694CF459DCFB4B0128514C2F3442B%40exadv11.host.magwien.gv.at
but it was rejected.
Perhaps you could come up with a better version.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
g/. Where they could be?
You would call "initdb" or "postgresql-10-setup" to create a new, empty
cluster in version 10 and use that with -D.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
coming and unfriendly community.
>
> Banning should be reserved for the most serious and abusive cases.
+1
I personally find Hemil's attitude and his persistent refusal to
bottom-post more disruptive than the original question.
pgsql-general should remain a low-threshold forum where n
u start it using "Type=oneshot" and "pg_ctl" or "Type=exec" and "postgres",
you don't need the systemd callback.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
y would you want to close such
connections before the database session ends?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
matically run that on all databases.
Avoid large objects if you can.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
t. :-(
You'll have to examine the application and see which tables reference the
large objects. Looking for columns with type "oid" will help.
I recommend that you add an "ON UPDATE OR DELETE" trigger that unlinks
orphaned large objects.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
o, it won't. It will just be faster.
That sounds like your database consists almost exclusively of large objects...
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
the application.
No, the service file is part of libpq, and Npgsql doesn't use that.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
s?
Either they don't or they write their own class that does that.
Perhaps you should contact Npgsql and suggest such a feature.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
lternatively, use the -B option of pg_dump to skip dumping
large objects.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Tue, 2021-02-02 at 13:44 +0530, Atul Kumar wrote:
> Is there any other option to increase the speed of vacuum?
For autovacuum, decrease "autovacuum_vacuum_cost_delay".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
th
the same count. But count(id) is more expensive, because it
will perform this unnecessary NULLness check for each row.
In short: use count(*) if you want to count rows, and use
count(x) if you want to count all rows where x IS NOT NULL.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Tue, 2021-02-02 at 00:20 +0200, Andrus wrote:
> > Obviously large objects *are* used.
>
> How to figure out what is this large object ?
You can extract it with
\lo_export 200936761 'somefile'
in psql and examine the file.
Ask the people who use that database
file size is 0 .
So? What is your point?
Somebody created a large object of size 0.
Don't ask me who did it, ask the people who use this database.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
e an index to use and it seems to
> > be choosing one of smaller size which leads to less IO and hence returns
> > the result faster.
>
> Would you mind throwing in a test for
>
> select count(1) ...
No need to test. That will be slightly slower because it repeat
> after running that command I was not able to connect the database
> using psql for few minutes.
That is to be expected.
If you have 300 processes performing I/O and using CPU, your machine
will vertainly be overloaded.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
?
>
> Postgres Version 9.6
VACUUM (FULL) will re-create the indexes too, and "maintenance_work_mem"
has an impact on index build speed.
But I have no explanation for such a large difference.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ivity but has a lower limit of "min_wal_size".
If your workload is subject to long lulls followed by activity spikes,
raising "min_wal_size" can improve the performance at the beginning
of an activity spike.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
play/blob/master/database.c#L404
but it certainly is not the most concise example.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
the UPDATE statement.
But you can do that in CREATE TRIGGER:
CREATE TRIGGER ... BEFORE UPDATE OF (c3) ON mytable ...
Of course that might mean that you cannot do conditional processing
in the trigger function, but you have to define several triggers
(which could perhaps share a trigger function).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
a single ID) and subpartition by timestamp.
This will speed up the queries by ID, since only a single partition
will be searched. You could then almost be as fast as with a single
monolitic table. Don't forget that the speed of an index scan does
not (or almost not) depend on the size of the table.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
list.
There must be really large data in this table, so it takes a long time to fetch
and
sort the rows. Try selecting only the columns you need.
Alternatively, add a LIMIT clause. Do you really need all 80 rows?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
out which partitions to scan takes planning time.
Very few queries become notably faster through partitioning.
Mostly those are aggregates and joins that match the partitioning scheme,
and sequential scans that include the partitioning key.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Thu, 2021-02-11 at 08:57 +, arunkumar.sampathku...@cognizant.com wrote:
> We would like to know the best practice to maintain PostgreSQL DB health so
> that there is no delay in Jabber chat messages.
You should hire a knowledgeable DBA.
Yours,
Laurenz Albe
--
Cybertec |
ition pruning: if the optimizer thinks that
it will have to scan a lot of partitions, it will plan a lot of workers.
But if the executor reduces that number to 1, we end up with way too
many workers.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
trusted certificate. No password prompt will be sent to the client. The cn
(Common Name)
attribute of the certificate will be compared to the requested database user
name, and if they
match the login will be allowed.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
st have
bound to
"DC=company,DC=example,DC=com?sAMAccountName" as the "ldapbinddn" first.
What I would do is experiment with the "ldapsearch" executable from OpenLDAP
and see
if you can reproduce the problem from the command line.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
e difference between primary and standby is not
that a
different number of multixacts are created, but that you need to read them on
the standby and not on the primary.
Are the multixacts caused by foreign keys or by subtransactions?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Mon, 2021-02-15 at 08:03 -0800, Christophe Pettus wrote:
> On Feb 15, 2021, at 07:47, Laurenz Albe wrote:
> > So my guess would be that the difference between primary and standby is not
> > that a
> > different number of multixacts are created, but that you need to read
On Mon, 2021-02-15 at 12:40 -0800, Christophe Pettus wrote:
> > On Feb 15, 2021, at 08:15, Laurenz Albe wrote:
> > Right. I cannot think of any other reason, given that the standby only
> > allows reading. It's just an "xmax", and PostgreSQL needs to read the
ed
> hard links with old cluster.
It is absolutely safe.
It would actually be quite unsafe *not* to delete the old cluster,
because if anybody managed to start it, data corruption would be the
consequence.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Tue, 2021-02-16 at 16:11 -0600, Ron wrote:
> SQL is only intuitive to people who've done programming... :)
SQL is quite counter-intuitive to people who have only done
procedural programming.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
user specific config values
> that would become accessible to the "SHOW " SQL comand.
_product_name := current_setting('company.product');
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ose that field is not "resettable" by the administrator, as I
> tried to, but then what drives changes?
SELECT pg_stat_reset_shared('archiver');
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Thu, 2021-02-18 at 11:42 +, Joao Miguel Ferreira wrote:
> On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe
> wrote:
> > On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote:
> > > I have a few PL/pgSQL functions that use queires like "SHOW
> > >
greSQL will use as many of the planned workers
as are currently available (max_parallel_workers).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
r"
to limit the number of parallel workers available to a single query.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
_0400_job | table | college
> > > sys| scheduler_0450_job_argument | table | college
>
> yes I know that, but my doubt is why \dt is showing tables of other
> schemas even I am setting the search_path.
The problem is that you are not running PostgreS
unning different C libraries or C library versions, so that the collations
with the same name behave slightly differently.
That could lead to the observed behavior with indexes on string data types.
Make sure to use the same C library version on both systems.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Fri, 2021-02-26 at 13:12 +0200, Yambu wrote:
> Is there a quick way to list tables used by a function if the function is big
> to search for tables manually?
No.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
the
session that created them, so you can safely ignore these errors.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
to install PostgreSQL packages from your Linux
distribution.
Your only problem is that /usr/pgsql-14/bin is not in your PATH environment
variable.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
RABLE;
>
> SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
A transaction started with the first statement will not take any SI locks, nor
will it ever receive a serialization error.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
action which took the lock.
The first thing you should do is to make sure that all your database
transactions are short.
Also, you should nevr explicitly lock tables. Table locks are taken
automatically
by the SQL statements you are executing.
Yours,
Laurenz Albe
--
Cybertec | https://
ication without the need of base back up again?
There are three ways:
1. have a WAL archive and configure "restore_command" on the standby
2. set "wal_keep_size" on the primary high enough
3. use a replication slot
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
"max_wal_size" is not the size of a WAL segment.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
e when people explicitly lock a table
it is not the correct solution.
Yours,
Laurenz Albe
Tom prefers wine: https://postgr.es/m/14929.1358317...@sss.pgh.pa.us
Yours,
Laurenz Albe
e lock when finished. Could someone hit me with a clue-stick?
You'd have to start a transaction and
LOCK tab IN SHARE MODE;
Commit the transaction to release the lock.
However, it is a bad idea to keep transactions with high locks open for a long
time.
Your real problem might have a better
ave nothing inside a database transaction
that *could* take a long time (even if it is normally fast).
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:
> Is it possible to repair this?
Not unless you tell us what exactly you did, command by command.
Yours,
Laurenz Albe
On Wed, 2022-04-06 at 18:41 +0200, W.P. wrote:
> W dniu 06.04.2022 o 12:37, Laurenz Albe pisze:
> > On Wed, 2022-04-06 at 10:57 +0200, W.P. wrote:
> > > Is it possible to repair this?
> > Not unless you tell us what exactly you did, command by command.
> >
ATE runs in a transaction that hasn't been committed
b) the SELECT is running on a standby server, and there is replication lag
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
here feedback re the sanity of this approach.
I thought that an unlogged table is rendered empty by recovery,
but perhaps I misunderstood what you are doing.
Yours,
Laurenz Albe
paround VACUUM is a routine activity and does not
interfere with DML, just like a normal VACUUM.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
use "search_path" is empty to
avoid security problems.
Fix your function:
ALTER FUNCTION trigger_function() SET search_path = public;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Fri, 2022-04-22 at 15:26 +0200, Willy-Bas Loos wrote:
> On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe wrote:
> >
> > The trigger function is bad and dangerous, because it relies on the current
> > setting of "search_path".
> >
> > You no
tchover to move to a current operating system on a
different
machine
- REINDEX CONCURRENTLY all indexes on string expressions
You could get data corruption and bad query results between the second and the
third steps,
so keep that interval short.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
mpty
> > "query"?
> >
> > https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
>
> They've never executed a query. Probably due to connection pooling opening
> connections for the pool but never needing to hand them out.
An alternative option is that the last query sent was an empty string.
Yours,
Laurenz Albe
301 - 400 of 1219 matches
Mail list logo
