min WITH
LOGIN
SUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION:
GRANT db1_owner, db2_owner, ... to clusteradmin;
It havent created any large objects.
How to use this information to fix the issue ?
Andrus.
ion
It havent created any large objects.
What hasn't created large objects?
I
How to use this information to fix the issue ?
Do the pg_dump as user clusteradmin.
This works. However I need to allow non-supoeruser to create backup
also. How to do this ?
Andrus.
152
200936767 30152
How to figure out what are large object with oids 200936761, 200936762
and 200936767 ?
Pd_dump throws error on first of them: 200936761
Andrus.
class AS pc ON pa.attrelid = pc.oid
WHERE
atttypid = 'oid'::regtype
AND relnamespace in ( 'public'::regnamespace, 'firma74'::regnamespace )
AND attnum > 0;
It returs 0 rows.
Andrus.
gresql.conf to
lo_compat_privileges=on as temporary fix.
dumpuser has created backups of same two schemas for years without
issues. The issue starts to occur today.
Application does not create large objects. It uses bytea columns instead.
How to figure out what is this large object ?
Andrus.
can figure it out.
There is only one function , lo_get() in this page which returns data. I
tried
select * from lo_get(200936761);
select * from lo_get(200936762);
select * from lo_get(200936767);
Those queries returned one row containing one zero-length column lo_get.
Andrus.
e200936761'
lo_export
sba=# \q
root@c202-76:~# ls -l large*
-rw-r--r-- 1 root root 0 veebr 2 10:45 large200936761
result file size is 0 .
Andrus.
s a superuser?
> Do you see anything in the data field?
select * from pg_largeobject
running as superuser returs empty table with 3 columns:
loid, pageno and data
Andrus.
ybe this causes orphan large object creation by server or by odbc
driver. How to fix this ?
report table shoud not have lo type columns. No idea why this cast is
generated using psqlodbc
Andrus.
table.
Why it adds cast to lo type ? This type does not exist in Postgres
server and causes server error.
Andrus.
ck due to unexisting lo type.
C7=0 (bytea as logvarbinary is false) is used in connection string.
Andrus.
(
set jit to off;
select ... from cartdata, ... where
set jit to on
)
But this cause syntax error.
How to turn off jit for specific subquery in Postgres 12 ?
Subquery is generated by EF Core and splitting it to multiple statements
is not possible.
Postgres upgrade is not planned.
Andrus
condition bug which was fixed in
later Postgres 9 version.
However this issue still exists in Postgres 13.1
Andrus.
Hi!
There is no other process inserting to this table?
There may be other processes in this server trying to insert same
primary key value (server name).
Last inserted row data should remain.
Andrus.
.
>Last inserted row data should remain.
I'm not understanding the above.
This table should contain last login time, user and ip address. It
should be updated by every process on login. For this old entry is
removed if it exists and new entry with same primary key is added.
Andrus.
his code from multiple processes to try
reproduce the error.
Andrus.
C++ build 1914, 64-bit
and psqlODBC driver.
Andrus.
27;%,'||trim(artliik.liiginrlki)||',%'
returns date for single integer list only.
How to change join so that type ranges in list like 6-9 are also returned?
Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report.
Postgres 13 is used.
Posted also in
https://stackoverflow.com/questions/72404218/how-determine-if-range-list-contains-specified-integer
Andrus.
);
$f$ LANGUAGE SQL ;
torusbpchar(charcol) still returns text data type.
npgsql DataReader is used to get data.
Andrus.
c', 'test', 'charcol') )
FROM Test
as Adrian Klaver recommends in
https://stackoverflow.com/questions/74061290/how-to-return-argument-datatype-from-sql-function#comment130780708_74061290
at this worked. In this best solution?
How to remove p_namespace parameter from colwidth()? ColWidth() should
return column width in first search_path table just like select ... from
test finds table test.
Andrus.
should it replaced with dynamic query like
execute 'select ' || p_field || ' from ' || p_table || ' limit 0'
and get column size from this query result somehow ?
Andrus.
gres. ColWidth is immutable and called with
constant arguments so it should work. How to fix postgres to allow
constant ColWidth() expression in cast ?
Andrus.
postgresql-12
How to install Postgres 12 in Debian 11.5 ?
Will this replication work since Debian versions are different but
hopefully locale implementations are same ?
Which is most reasonable way to replicate whole Postgres 12/Debian 10.3
cluster in Windows 11 ?
Andrus.
yback can continue?
Master server is in Postgres 12 in Debian 10.3
Hot standby in Postgres 12 in WSL Debian 11.5
Can Debian version 10.3/11.5 difference cause this exception?
Andrus.
SL
Client is pg_dump from Postgres 15 running in Windows 11
Andrus
e are no file size limits. Error
message is not about this.
Backup client is running in Windows 11 and this cannot changed.
How to create backup in format from which tables can selectively restored?
Posted also in
https://stackoverflow.com/questions/75387616/how-to-make-directory-format-backup
Andrus.
restore using custom format are much slower.
Andrus.
--
Erik
ery used as an expression
How to pass set of integers to function? Should temp table with fixed
name used or is there better solution?
Using Postgresql 12+
Andrus.
xt()', x,nsa))[1]::text::numeric AS tasusumma
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
nsa
FROM t
) Ntry
Andrus.
summa
works.
How to make it work in both versions?
Is it possible add some CASE WHEN or IF command or any other idea ?
Andrus.
numeric path 'ns:Amt')
In 9.1.5 this throws error
ERROR: syntax error at or near "("
LINE 20: lateral xmltable(xmlnamespaces(
^
SQL state: 42601
Character: 582
Andrus.
old Debian squueze.
Client accesses server from Windows 10 using psqlODBC driver with
sslmode=allow in connection string.
How to verify that connection is encrypted ? Is there some command in client
or server or can some protocol analyzer used if no easier way?
Andrus.
Posted also
sions ?
Andrus.
possible, if this helps.
Tried also using like:
WHERE toode.toode=vordlusajuhinnak.toode OR
toode.toode LIKE vordlusajuhinnak.toode||'/%'
Posted also in
https://stackoverflow.com/questions/76311957/how-to-match-character-columns-with-separated-subcolumns
Andrus.
gt; Parallel Index Only Scan using toode_pkey on toode
(cost=0.55..95029.93 rows=303869 width=60)"
" -> Seq Scan on vordlusajuhinnak (cost=0.00..721.33 rows=39433
width=32)"
How to speed it up?
Andrus.
23.05.2023 14:32 Bzm@g kirjutas:
Great,
However I think it
uhinnak.toode;
used and keeping existing table structure? Functional index should
produce same speed improvement as using separate column?
Andrus.
Hi!
Using index
create index on toode ( *split_part( toode, '/',1) *)
and query
select toode.toode , n2, n3, n4
from toode, vordlusajuhinnak
where *split_part( toode.toode, '/',1) *= vordlusajuhinnak.toode;
reduces run time to 5 minutes.
Andrus.
23.05.2023 17:26 A
/questions/76394896/how-to-remove-unnecessary-grant-and-revoke-privileges-from-table
Andrus.
Hi!
Or just execute those REVOKE ALL commands (except for PUBLIC) that pgAdmin
already gives you.
pgAdmin gives
REVOKE ALL ON TABLE public.kaspriv FROM someuser;
I ran it but pgAdmin still gives this statement.
Andrus.
Hi!
I tried
alter role alekspoluh reset all
After this command pgAdmin still shows revoke and grant commands for
alekspoluh role.
How to remove all grant and revoke assignments for role ?
Andrus.
03.06.2023 20:50 Andrus kirjutas:
Hi!
> REVOKE must be executed by the gran
users like
GRANT SELECT ON TABLE public.kaspriv TO paide;
How to remove user-spefic grants ?
Andrus.
min2;
pgAdmin shows revoke commands for those users:
REVOKE ALL ON TABLE public.kaspriv FROM admin1;
REVOKE ALL ON TABLE public.kaspriv FROM admin2;
How to prevent pgAdmin to show those revokes?
Andrus.
cting Refresh does
nothing.
Smells like a bug.
Right clicking in Tables and selecting Refresh worked.
Andrus.
ebian 16.4-1.pgdg120+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0
Posted also in
https://stackoverflow.com/questions/78938204/how-to-grant-role-to-user
Andrus.
process working on that table.
It looks like other process has added same row during trancaction.
How to change script so that it works starting at Postgres 9.0 and does
not cause error in this case ?
Andrus.
user
name. In this case one row should remain.
I can just ignore duplicate key error on commit but maybe there is some
better way not to cause error.
Andrus.
ated for this ?
After insert if other process inserts same key value, transaction still
ways. Should manual locking used or is there better method.
Andrus.
better method.
>I don't follow what you mean.
Allow access to this table for single transaction only. Wait for
exclusive lock , do update/insert, release exclusive lock.
Andrus.
g_wal/00010005000000EB": Permission denied
2021-03-05 12:10:30.626 EET [4580] LOG: could not rename file
"pg_wal/0001000500EC": Permission denied
How to fix this ?
It looks like database is working normally, users havent reported any
issues.
Andrus.
wal also contains files with .deleted extension like
0001000500B2.deleted
Andrus.
tel 67 files in pg_wal. Will
postgres remove .deleted files automatically or should I create windows
task which deletes them periodically ?
Andrus.
021-03-06 19:46:19 EET checkpointer LOG: could not rename file
"pg_wal/000100070016": Permission denied
Should chekpointer process terminated to force it to use new setting. Is
it safe to kill it during database usage.
Andrus.
files with .deleted extension deleted manually to save disk space
? May of them have dates before today.
Andrus.
ORDER BY
CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
THEN pg_catalog.pg_database_size(d.datname)
ELSE NULL
END DESC -- nulls first
Andrus.
blocks to find changed
blocks to backup. So it should not cause permission denied errors after
every 10 seconds.
Andrus.
and threads which use files in pg_wal
directory ?
Is there some utility for this or can restart manager or other API used
to create such log?
Andrus.
is regular postgres process which is
in idle state and waiting for ClientRead event. It has executed last
query an hour ago.
It looks like wal files are not released after usage.
Andrus.
00A0072
postgres.exe 20048 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0071
postgres.exe 30156 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0071
postgres.exe 26976 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0071
postgres.exe 26312 File C:\Program
Files\PostgreSQL\13\data\pg_wal\0001000A0074
...
Many wal files are used by multiple processes.
Andrus.
indows but error is the same.
How to recover data from 9.3 linux directory ?
Andrus.
ta from 32 or 64 bit server ?
Should I create virtual machine and install Linux with 9.3 into it or is
there simpler solution?
Will data from 32 bit server require 32 bit Linux with 32 bit postgres
or will it work with 64 bit Linux also ?
Andrus.
to it. How to install
postgres 9.3 in new Debian ?
Andrus.
stall it in new Debian ?
Or is it possible to download ISO image with Postgres 9.3 installed ?
Or should I try to find old Debian ISO image and install it into this?
Andrus.
pt/FAQ#Where_are_older_versions_of_the_packages.3F
<https://wiki.postgresql.org/wiki/Apt/FAQ#Where_are_older_versions_of_the_packages.3F>
I discovered that Debian allows to install 9.3 side-by side with 12
apt-get install postgresql-9.3
Andrus.
04F0082": Permission denied
So It should be probably reproducible in any Windows 2019 server.
Andrus.
on those servers.
In one of those servers, do you have in pg_wal/ some files named
xlogtemp.N? N is an integer that would be the PID of the process that
generated it.
No. Intel server has 4 files with .deleted extension. AMD server has no
.deleted files. It has probably lower traffic.
Andrus.
with them.
Andrus.
Hi
>I am not completely sure which flags your installation has, but
pg_config --configure outputs
--enable-thread-safety --enable-nls --with-ldap --with-openssl
--with-uuid --with-libxml --with-libxslt --with-icu --with-tcl
--with-perl --with-python
Andrus.
>On Wed, Mar 17, 2021 at 09:25:00AM +0200, Andrus wrote:
pg_config --configure outputs
--enable-thread-safety --enable-nls --with-ldap --with-openssl --with-uuid
--with-libxml --with-libxslt --with-icu --with-tcl --with-perl --with-python
Thanks. Do you actually use OpenSSL,
Not direc
red Access: Read Attributes, Delete, Synchronize,
Disposition: Open, Options: Synchronous IO Non-Alert, Open Reparse
Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a
Andrus.
17:07:09,9668956 postgres.exe 11800 QueryDirectory C:\Program
Files\PostgreSQL\13\data\pg_wal NO MORE FILES FileInformationClass:
FileBothDirectoryInformation
Andrus.
quot;
"9","","0x7ffc7639af04","0x7ffc7639af04",""
"10","","0x7ffc7728fe86","0x7ffc7728fe86",""
"11","","0x7ffc7728f622","0x7ffc7728f622",""
"12","","0x7ffc77290a46","0x7ffc77290a46",""
"13","","0x14048ccca","0x14048ccca",""
"14","","0x14009463b","0x14009463b",""
"15","","0x140094365","0x140094365",""
"16","","0x14008e541","0x14008e541",""
"17","","0x140286f46","0x140286f46",""
"18","","0x1400a17ca","0x1400a17ca",""
"19","","0x1402923cb","0x1402923cb",""
"20","","0x1401b95c0","0x1401b95c0",""
"21","","0x14049f304","0x14049f304",""
"22","","0x7ffc77bb7974","0x7ffc77bb7974",""
"23","","0x7ffc7a13a2d1","0x7ffc7a13a2d1",""
It looks like too small buffer is passed to NtQueryInformationFile .
Andrus.
Hi!
Okay, cool. I am going to send you privately two links to the builds
I am going to produce, 13.2 unpatched and 13.2 patched.
I replaced files in 13.1 server with ones from your patched version.
There are no errors in log file now for 8 hours.
Andrus.
Hi!
Have you tested the unpatched builds?
No.
Andrus.
ot rename
temporary statistics file "pg_stat_tmp/global.tmp" to
"pg_stat_tmp/global.stat": Permission denied
Andrus.
s file "pg_stat_tmp/global.tmp" to
"pg_stat_tmp/global.stat": Permission denied
2021-03-21 23:51:25 EET autovacuum worker LOG: using stale
statistics instead of current ones because stats collector is not responding
Andrus.
ompiled by Visual C++ build 1900, 64-bit
Posted also in
https://stackoverflow.com/questions/67683299/on-conflict-do-nothing-clause-is-ignored-on-insert
Andrus.
of
hours column in hours table for every person.
Note that since hours running total becomes greater than 120 in job 2,
job 2 hours should appear in both hours columns.
Maybe window functions can used.
Andrus.
column
in hours table for every person.
Andrus.
13.02.2022 14:46 Torsten Förtsch kirjutas:
something like
SELECT *
, least(sum(hours) OVER w, 120) AS regular
, greatest(sum(hours) OVER w - 120, 0) AS overtime
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id);
job_id
Hi!
It worked.
Thank you very much.
Andrus.
13.02.2022 16:46 Torsten Förtsch kirjutas:
WITH x AS (
SELECT *
, sum(hours) OVER w AS s
FROM hours
WINDOW w AS (PARTITION BY person ORDER BY job_id)
)
SELECT *
, greatest(least(s, 120) - coalesce(lag(s, 1) OVER w, 0), 0) AS
is there some exisising wal decoder in C# or as command-line utility?
Posted also in
https://stackoverflow.com/questions/79518438/how-to-convert-wal-log-to-sql-commands-from-given-time
Andrus
used
only to log on to postgres without password.
[1]:
https://docs.devart.com/studio-for-postgresql/connecting-to-db/generating-ssl-certificate.html
Andrus
101 - 181 of 181 matches
Mail list logo
