NULL (unknown) as well. That is much better than any "zero" value
which would lead to an undesired result.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
references to the source deliberately.
Since you say you like PostgreSQL and are someone who wants to understand what
is
going on, your way will eventually lead to the source code.
Many of the error messages only make sense if you understand how PostgreSQL
works,
e.g. that there is a WAL receiver process that cannot proceed if the master
server is shut down.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
th rebuilding the standby later.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ticated user, query foreign table - ERROR, server
> crashes with signal 11: Segmentation fault error when I quit the psql session
Are the "postgres" executable and libpq linked with the same version of
OpenLDAP?
Any other extensions installed?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
n the GUI level, while the C API is pretty
much the same.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
wned and controlled by Oracle, but free open source.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
> For updates, MySQL avoids a lot of index write overhead. PostgreSQL has more
> overhead per update.
That is what I meant when I said that PostgreSQL is less suitable for a
key-value store.
There is HOT update which can mitigate the problem if the updated columns are
not indexed.
Yo
us_commit = off", I can imagine that it could matter.
It is not a matter of how many clients there are, but of how
often a new writing transaction is started.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
re several hundret MByte)
Ideally outside the database, if they are many.
Large databases are harder to backup than large file systems.
If you keep 20MB binaries in the database, you'd use the "bytea" data type.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
nitdb", dump and restore to switch to checksums.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
t; 3. What is the best way to run VACUUM FULL with less window.
Identify which tables really need it rather than VACUUMing everything.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
8 cores, all of which are 90% busy.
Try to profile the server ("perf" on Linux) to see where the time is spent.
Are there any foreign key constraints pointing to the table being updated?
Then make sure that either no key column is updates or that the foreign
keys are indexed.
Y
therefore you will have more disk-io when you read from such an index.
To be more explicit: if you can live with a slightly less efficient
index scan and want fast data modifications, use only the second index.
If you hardly ever update the table, don't mind the wasted space and
want e
as an expression that involves several columns of the base table.
You'd have to parse pg_rewrite.ev_action.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
make install
> command. After restarting the service, all will work as expected with the
> same database objects that were running before?
If you use the same major release and configured it identically on the same
architecture, yes.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
QL was configured.
>
> Do you know if it possible to run a query or something else because pg_config
> isn’t in the binaries.
> I’ve saw it but is was of version 10 or 11, not the 9.6
You have to install the package that contains the headers and development tools
doe that.
Usually it is called *-dev or *-devel.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ool,
> but I can't figure out how to get it. I see the following RPM (the ones
> with an 'i' or 'i+' are installed):
>
[...]
> i | postgresql10-devel | PostgreSQL development header files and->
> | package
That would be the one with pg_config in it.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
vacuumed fast enough.
Other than that, I don't really see the need for keeping a permanent
"blueprint" table
around; all this can lead to is confusion. True, it is cute to use CREATE
TABLE ... (LIKE ...),
but a few lines more in your code won't kill you. Particularly since you have
that only once
in your code, right?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ocedures against the temporary table.
Yes, that would cause a problem.
The SQL statement "DISCARD PLANS" should fix the problem.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
emote logins
with that user.
But for your application users LDAP authentication is a fine thing, and not
hard to set up if you know a little bit about LDAP.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
the hole through which the attacker crept in.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
as stupid
enough not to notice right away).
Then, please don't top-post on these lists. Write your reply *below* what you
quote.
What exactly is your problem? "We are getting issues" is not detailed enough.
You probably just have to get the encoding right.
Yours,
Lau
his answer can help you:
https://stackoverflow.com/a/39204667/6464308
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
l
mess with your log files and you should be able to find out more about the
problem.
I never use logrotate with PostgreSQL, but set "log_filename" to
"postgresql-%a.log"
or "postgresql-%d.log", "log_truncate_on_rotation" to "on" and
"log_rotation_size" to
"0". Then PostgreSQL rotates the log by itself.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ger than that?
Try to experiment with lower settings. It will cause marginally more
network traffic, but dead connections will be detected more quickly.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
by a normal user that has the EXECUTE privilege
on the function.
Don't forget to "SET search_path" on such a function (as mentioned in the
documentation). It might also be a good idea to REVOKE EXECUTE on the
function from PUBLIC.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
EDB privilege.
>
> Thanks, that's a great idea! Is this pattern documented anywhere as a
> complete finished thing?
I'm afraid that is left as an exercise to the reader.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
hing else.
What is the error message in the log?
What do you see in /proc/12345/limits and /proc/12345/cgroup
(assuming that your postmaster PID is 12345)?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ype.
You'll have to specify an array of which type you want, probably
... RETURNS text[]
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
nnect timeout occurred
Just guessing:
Perhaps there is a materialized view that is based on a foreign table,
and when PostgreSQL tries to populate the materialized view during
the restore, it fails to connect to the remote database.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
he boot sequence, IPv6 was disabled, so no more statistics
could be collected. Since it is an UDP socket, there were no errors.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
file.
The default is a tab character in text format, a comma in CSV format.
This must be a single one-byte character. This option is not allowed when
using binary format.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
names or ports,
and you can also use "target_session_attrs".
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
types are binary coercible (the storage ist the same).
> 3.The column ratified is of type character varying(1). Why is it casted
> to text?
See 2. above.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
ALTER TABLE mytable ALTER id ADD GENERATED ALWAYS AS IDENTITY;
That would not rewrite the table, just "relabel" the type name
to "integer" and then convert it to an identity column.
Why do you want that extra level of obfuscation rather than
calling an integer an integer?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
pport it anyway.
Yours,
Laurenz Albe
PostgreSQL
> backend recompress them when TOAST'ed. That's a waste of CPU and IO
> bandwidth...
That's not what you were looking for, but why not store the compressed data
in the database (after SET STORAGE EXTERNAL on the column) and uncompress
them after you have received them on the client side?
Yours,
Laurenz Albe
ever,
> when I try to read
> the result from the parent thread, the program crashes with a segmentation
> fault.
That's too little information.
Yours,
Laurenz Albe
roblem with a storage driver,
file system or some other low-level software component.
It might of course be a PostgreSQL bug too, but it is hard to say
without a way to reproduce...
Yours,
Laurenz Albe
t;
> What are the cases where postgres may grow without caring about
> temp_file_limit ?
That's too little information for a decent answer.
One obvious answer is: if it is not writing temporary files.
Yours,
Laurenz Albe
this a safe configuration to avoid index corruption, and other
> problems, while still being compatible with the previous locale
> settings?
Yes, that is safe.
But it is not compatible with the old setup when it comes to lc_time,
lc_messages and the others. You should use sv_SE.UTF-8 for these
locale categories.
Note that that won't cause problems with upgrading the C library.
Yours,
Laurenz Albe
The C collation is not good for natural
language sorting, but it is fast and not subject to collation changes.
Yours,
Laurenz Albe
ost=0.00..243466.06 rows=2965306 width=31)
> -> Parallel Hash (cost=243466.06..243466.06
> rows=2965306 width=34)
> -> Parallel Seq Scan on table_b t2
> (cost=0.00..243466.06 rows=2965306 width=34)
> JIT:
> Functions: 19
> Options: Inlining true, Optimization true, Expressions true, Deforming true
> (17 rows)
Perhaps parallel query drives you OOM. Does the problem also happen if
"max_parallel_workers_per_gather" is set to 0?
Yours,
Laurenz Albe
t;
> I tried with pg_recvlogical, and read
> https://www.postgresql.org/docs/current/protocol-replication.html but
> I don't see how I can make restart_lsn advance to anything.
You could shutdown the server and edit the file in "pg_replslot" with
a hex editor. Not very convenient, and you'd have to study the source
to understand the format of the file.
Yours,
Laurenz Albe
On Wed, 2023-05-17 at 08:21 +0200, hubert depesz lubaczewski wrote:
> On Wed, May 17, 2023 at 08:16:41AM +0200, Laurenz Albe wrote:
> > On Tue, 2023-05-16 at 19:35 +0200, hubert depesz lubaczewski wrote:
> > > > I'm working on a workaround for a bug in Pg
> >
ot;, which corresponds to the snapshot held by the oldest
query in any database on the standby server.
Yours,
Laurenz Albe
it.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/int.c;h=44d1c7ad0c4d770acc33f35bb57080bac7670e5c;hb=HEAD#l1499
This is the "integer" version of the function, the others are in
"int8.c", "numeric.c" and "timestamp.c".
Yours,
Laurenz Albe
ould appreciate any assistance you are able
> to provide for this.
I have never heard of that, but I'll reply on the -general list, where the
question is
more likely to reach the people who know.
Yours,
Laurenz Albe
But this is more about ticking off checkboxes, right?
Yours,
Laurenz Albe
o the easy way of using several database connections to run the
parallel COPY statements?
Yours,
Laurenz Albe
e user)
If the server has already closed the connection, nothing much will happen.
If the client never sends ACK to the FIN, the session will still time out.
You can fiddle with the tcp_keepalives_* parameters to influence that.
Yours,
Laurenz Albe
this way I've a dominan of composite type that contain others
> domain... what do you think?
Avoid using composite types as data types for a table column.
It adds complexity for no clear gain.
Yours,
Laurenz Albe
gt;
> Talking about first point I could use the "table inheritance", but I've to
> reset all constraints for each table :-(
Inheritance is actually a very good way to do this.
You don't inherit constraints, but at least the column definitions.
Yours,
Laurenz Albe
ovacuum that caused your
concern,
but afterwards you will never again have a long-running, intense autovacuum run
on
that table.
Yours,
Laurenz Albe
step process, if you create a view on the table that uses
to_date() to convert the column to a "date".
Yours,
Laurenz Albe
On Fri, 2023-06-16 at 09:40 +, Brainmue wrote:
> I am currently looking for a solution similar to Oracle Listener.
Can you explain why? Perhaps there exists a good solution for the
underlying problem.
Yours,
Laurenz Albe
l.org/docs/current/libpq-ldap.html
Yours,
Laurenz Albe
On Fri, 2023-06-16 at 14:49 +, Brainmue wrote:
> 16. Juni 2023 14:50, "Laurenz Albe" schrieb:
>
> > On Fri, 2023-06-16 at 12:35 +, Brainmue wrote:
> >
> > > We want to minimise dependencies between the application and the
> > > associ
On Fri, 2023-06-16 at 09:04 -0500, Ron wrote:
> On 6/16/23 07:50, Laurenz Albe wrote:
> > On Fri, 2023-06-16 at 12:35 +, Brainmue wrote:
> > > We want to minimise dependencies between the application and the
> > > associated PostgreSQL DB.
> > > The idea
On Fri, 2023-06-16 at 15:25 +0200, Dominique Devienne wrote:
> On Fri, Jun 16, 2023 at 2:50 PM Laurenz Albe wrote:
> > There is an existing solution for that: the libpq connection service file:
> > https://www.postgresql.org/docs/current/libpq-pgservice.html
>
> The problem
postgres -p 5432 -c "select
> pg_is_in_recovery"
>
> I don't get any errors and get the expected output as "t".
>
> Note: the passwords in and path of both pgpass files are identical.
>
> then why am I getting errors on the slave node for the same command even
> after having everything the same ?
pg_hba.conf could be different on both servers.
Yours,
Laurenz Albe
, DELETE, MERGE, or VALUES statement.
so NOTIFY is not supported. However, you need some inside knowledge to know
that what you are running is an "unnamed prepared statement" and that the
limitation
stated in PREPARE applies.
Yours,
Laurenz Albe
that primary database can
> have locally
> committed data when it is waiting on SYNC and receive the cancel signal from
> the application,
> it can be helpful.
I don't think that's anywhere in the documentation.
Yours,
Laurenz Albe
is this proposal:
https://www.postgresql.org/message-id/flat/CALj2ACUrOB59QaE6%3DjF2cFAyv1MR7fzD8tr4YM5%2BOwEYG1SNzA%40mail.gmail.com
Yours,
Laurenz Albe
registry)::text) ~~*
> '%nse%'::text)))
> Rows Removed by Filter: 6226870
> Buffers: shared hit=4322296 read=1608998 dirtied=1 written=1247
> Planning Time: 0.891 ms
> Execution Time: 86509.070 ms
> (10 rows)
>
> It's not using our gin index at all, but the btree one.
The problem is that PostgreSQL estimates that the index scan will return 11214
rows, when it is actually one. This makes the plan to scan the table using
an index that matches the ORDER BY clause appealing: we might find 100 rows
quickly and avoid a sort.
You can try to improve the estimates with more detailed statistics,
but if that doesn't do the job, you can modify the ORDER BY clause so
that it cannot use the bad index:
ORDER BY res_partner.display_name ,res_partner.id + 0
Yours,
Laurenz Albe
On Wed, 2023-06-28 at 12:01 +0200, David Tinker wrote:
> Is it ok to use physical replication between different 15.x minor releases
> (on Ubuntu 22.04)? I haven't been able to find a definitive answer.
Yes, that is OK.
Yours,
Laurenz Albe
e active as much as possible.
That will improve throughput.
Yours,
Laurenz Albe
bet is to create an index that covers both WHERE
conditions,
or a covering index, which will get you the best result:
CREATE INDEX ON table1 (numfic, ladate) INCLUDE (cod, nb_obs);
Yours,
Laurenz Albe
On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote:
> Hi Laurenz, as said, in each partition there is only one value for ladate.
The planner doesn't seem to take that into account.
Yours,
Laurenz Albe
h logical decoding plugin are you using?
"pgoutput", which is provided by PostgreSQL, will surely emit properly
detoasted values.
Yours,
Laurenz Albe
0.0.0.0/0 md5"
Then you must have entered the wrong password.
If in doubt, change the password.
Yours,
Laurenz Albe
f EDB's special featurs, a simple pg_upgrade could
work.
Otherwise, things can become tricky.
Test well.
Yours,
Laurenz Albe
the query cannot see
the results from the previous statement.
The documentation is quite clear here:
It is generally unwise to mix read-only and read-write commands within
a single function using SPI; that could result in very confusing behavior,
since the read-only queries would not see the results of any database
updates done by the read-write queries.
Yours,
Laurenz Albe
ok
>
> The source cluster was not shut down cleanly.
> Failure, exiting
>
> Please how do you resolve this
Well, start the 9.5 server and shut it down cleanly.
Yours,
Laurenz Albe
king other
transactions for a long time, you can
SET lock_timeout = '1s';
DROP TABLE ...;
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
RYS = '01'
>
> The amount of data in the table is as follows.
> TBL_SHA 38700325
> TBL_INF 35546
This looks very much like it is a problem with the data types.
I see that you are using "character", which you shouldn't do.
What I cannot see is if the columns are defined as "character" or whether you
bind
the parameters as "character". Can you show us the table definition of
"TBL_SHA"
and "TBL_INF"?
Yours,
Laurenz Albe
On Thu, 2023-07-20 at 22:14 +0800, gzh wrote:
> The information I provided is incorrect, please see my previous reply.
My question remains: I would like to see the table definitions.
Also, did you ANALYZE the tables?
Yours,
Laurenz Albe
_CD = 'MLD009'::bpchar) AND (ETRYS =
> '0001'::bpchar))
> Rows Removed by Filter: 32000325
> Planning Time: 0.162 ms
> Execution Time: 124168.838 ms
Thanks. That should definitely use a b-tree index defined on (ms_cd, etrsys).
Did you change any parameters that have an impact on query planning?
You can see that in the output of EXPLAIN (ANALYZE, BUFFERS, SETTINGS).
Yours,
Laurenz Albe
"\d tablenane" in "psql").
Invalid indexes won't be used.
Yours,
Laurenz Albe
'1886088kB', jit = 'off', search_path =
> '"$user", mdb'
> [...]
No unusual settings. So that theory is dead.
> > One other idea: check if the index is INVALID (this will
> > be visible if you run "\d tablenane" in "psql").
&
On Mon, 2023-07-24 at 18:54 -0700, Badri Subramaniam wrote:
> Is there a way to turn off auto commit using the C API?
No.
Yours,
Laurenz Albe
grade the operating system.)
> Will the performance be as good as before I upgrade?
It should be at least as good, but sometimes it isn't.
That's why you test (and tune if necessary).
Yours,
Laurenz Albe
TRAINTS ALL DEFERRED is replicated,
no, it isn't.
Yours,
Laurenz Albe
ld cause more harm
than benefit.
First, as the code says, it doesn't make a lot of difference. And who knows,
perhaps
someone somewhere creates superusers, later changes them to NOSUPERUSER and
expects
CREATEDB and CREATEROLE to be set after that.
If anything, we could add something to the documentation.
Yours,
Laurenz Albe
On Tue, 2023-09-05 at 00:03 +0200, Erik Wienhold wrote:
> On 04/09/2023 03:42 CEST Tom Lane wrote:
> > I think the last hunk of this is plenty sufficient, and the earlier
> > ones just add noise.
>
> Done.
Looks good to me.
Yours,
Laurenz Albe
all the time, I'd try to use a
persistent table to
avoid catalog table bloat. If several function invocations need to store their
record set in
the same table, you could make the backend process ID part of the primary key.
Yours,
Laurenz Albe
On Wed, 2023-09-06 at 13:35 +0530, Daulat wrote:
> Do we have a max limit of partitions for a table in postgres?
I don't think there is a technical limit.
But as soon as you have more than a few thousand partitions, the
experience won't be that great any more.
Yours,
Laurenz Albe
reproducer, but still I am curious what is wrong here with me running
> an "ANALYZE" after my data import.
To fix the "pg_statistic" error:
- take down time
- set "allow_system_mods = on"
- TRUNCATE pg_statistic;
- ANALYZE;
You are lucky that the corrupted table is one that holds data that can be
rebuilt.
Yours,
Laurenz Albe
On Wed, 2023-09-06 at 10:33 +0200, Torsten Krah wrote:
> Am Mittwoch, dem 06.09.2023 um 10:21 +0200 schrieb Laurenz Albe:
> > You are lucky that the corrupted table is one that holds data that
> > can be rebuilt.
>
> It is a test instance / container anyway which is deleted
llation that does what you want explicitly:
SELECT upper('testµ' COLLATE "C");
upper
═══
TESTµ
(1 row)
But then don't complain if your "ö" and "é" are not uppercased either.
Yours,
Laurenz Albe
t;:SYS_B_04 AND
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_05 AND
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_06 AND
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_07 AND
> "HISTORYEVE0_"."EVENT_TYPE"<>:SYS_B_08 AND
> "HISTORYEVE0_"."IS_DELETED"=:SYS_B_00 AND
> (INTERNAL_FUNCTION("HISTORYEVE0_"."PRODUCT_VIEW") OR
> "HISTORYEVE0_"."PRODUCT_VIEW" IS NULL)))
>
> It finds the same 332 rows but it does only 20 logical reads.
In PostgreSQL, the 332 matching rows seem to me stored mostly on different
pages, while they are
better clustered in your Oracle table.
If it is really very important for you, and the 57 milliseconds for the index
scan is too much,
you can reorganize the table with
CLUSTER hist28.history_event_display_timestamp_20230301 USING
history_event_display_timesta_prism_guid_display_timestamp_idx1;
That should reduce the number of pages read.
Yours,
Laurenz Albe
My advice it not to worry.
But if you feel like worrying very much, go ahead and test your application
thoroughly with 12.14.
Yours,
Laurenz Albe
ot;. So the solution is to set "search_path"
empty:
SET search_path = '';
SELECT pg_get_viewdef('myschema.myview');
Yours,
Laurenz Albe
the
tables micht be an explanation. Since one of the functions is called
"check_...",
another explanation could be that you have check constraints that use functions
that
access other tables. That won't work and is not allowed.
Without knowing more, I cannot be certain what exactly is wrong, but it doesn't
look
like a PostgreSQL bug to me. Perhaps you can provide more details.
Yours,
Laurenz Albe
25.076 CEST [47] ERROR: could not open file
> "base/16386/17328": Interrupted system call
> 2023-09-20 10:38:41.897 CEST [49] ERROR: could not open file
> "base/16386/68359": Interrupted system call
Is that an NFS mount? What are the mount options?
Yours,
Laurenz Albe
he function itself, we
> refer to
> the accounts table, and if I am not mistaken, the whole point of pl/pgsql is
> that
> you can refer to tables, etc. so I don't think I have done anything 'illegal'.
Ok, so my conjecture about check constraints was wrong.
The fact remains that you have to restore the dump into an empty database.
Then these errors should not occur.
Yours,
Laurenz Albe
rable. The people on MySQL mailing lists or forums
might tell an entirely different story.
I would say something about license and free software, but if you plan to lock
yourself into the cage of a cloud hosted database, that probably doesn't matter
much.
Yours,
Laurenz Albe
gy for transaction management? or
> any
> different/better ones?
Nobody except Amazon can tell. It is closed source.
Yours,
Laurenz Albe
also be that this
is caused by modifications that Amazon did to PostgreSQL.
Yours,
Laurenz Albe
ication,
which may not be simple either.
Yours,
Laurenz Albe
601 - 700 of 1219 matches
Mail list logo
