On 2/2/21 9:05 AM, Andrus wrote:
Hi!
and used psqlodbc to insert this data:
create temp table test ( test bytea ) on commit drop;
insert into test values ( ?t.t );
This code throws exception
type "lo" does not exist
but each call adds new row to pg_largeobject_metadata table.
Odbc driver
Hi!
So at that point the deed has been done.
The questions to ask:
1) Why the driver thinks it is being passed a large object in the
first place?
Source data type was binary. It was mapped to oid for unknown reason.
2) Have there been any recent changes to code that passes through the
On 2/2/21 4:12 AM, Andrus wrote:
Hi!
So? What is your point?
Somebody created a large object of size 0.
report table has bytea column. It looks like psqlodbc driver adds ::lo
cast when inserting binary data:
https://github.com/hlinnaka/psqlodbc/blob/master/convert.c#L4564
and this add
Hi!
So? What is your point?
Somebody created a large object of size 0.
report table has bytea column. It looks like psqlodbc driver adds ::lo
cast when inserting binary data:
https://github.com/hlinnaka/psqlodbc/blob/master/convert.c#L4564
and this adds row to pg_largeobject_metadata t
On Tue, 2021-02-02 at 10:48 +0200, Andrus wrote:
> > You can extract it with
> > \lo_export 200936761 'somefile'
> >
> > in psql and examine the file.
> >
> > Ask the people who use that database!
>
> Tried
>
> root@c202-76:~# ./pgsqlkaiv.sh
>
> psql (12.2 (Debian 12.2-2.pgdg100+1))
> Type "
Hi!
I don't suppose this was done in a structured way that could be gone
back over?
Accidently '200936767'::lo cast was issued :
INSERT INTO report ( ... ) values (.. , '200936767'::lo, ... )
server throws error type "lo" does not exist for this.
Maybe this causes orphan large object
Hi!
>I have imported data from other clusters and executed lot of different
sql commands. I have used grant, revoke, reassign commands to change
privileges for other users and have deleted and added users.
I don't suppose this was done in a structured way that could be gone
back over?
E
Hi!
You can extract it with
\lo_export 200936761 'somefile'
in psql and examine the file.
Ask the people who use that database!
Tried
root@c202-76:~# ./pgsqlkaiv.sh
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.
sba=# \lo_export 200936761 'large200936761'
lo_export
sba=# \
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!
Yours,
Laurenz Albe
--
Cyber
On 2/1/21 3:07 PM, Andrus wrote:
Hi!
> What code changed between the last backup and today?
I have imported data from other clusters and executed lot of different
sql commands. I have used grant, revoke, reassign commands to change
privileges for other users and have deleted and added users
Hi!
> What code changed between the last backup and today?
I have imported data from other clusters and executed lot of different
sql commands. I have used grant, revoke, reassign commands to change
privileges for other users and have deleted and added users.
Cluster contains 25 databases.
On 2/1/21 2:20 PM, Andrus wrote:
Hi!
>Obviously large objects *are* used.
>You have to grant the database use permissions with
> GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser;
>Alternatively, use the -B option of pg_dump to skip dumping
>large objects.
I added -B option and changed
Hi!
>Obviously large objects *are* used.
>You have to grant the database use permissions with
> GRANT SELECT ON LARGE OBJECT 200936761 TO dumpuser;
>Alternatively, use the -B option of pg_dump to skip dumping
>large objects.
I added -B option and changed postgresql.conf to
lo_compat_priv
>I misspoke earlier about large objects not being tied to a
schema.table. They can be as a column of type oid.
To see if they are try :
SELECT
relname,
attname
FROM
pg_attribute AS pa
JOIN pg_class AS pc ON pa.attrelid = pc.oid
WHERE
atttypid = 'oid'::regtype
AND relname
Hi!
>Long term figure out what they are and if they are needed or not.
Non-superuser backup worked earlier. It looks like large objects
suddenly appeared in database:
select * from pg_largeobject_metadata
Oid Lomowner
200936761 30152
200936762 30152
20093676
On 2/1/21 1:28 PM, Andrus wrote:
Hi!
>Long term figure out what they are and if they are needed or not.
Non-superuser backup worked earlier. It looks like large objects
suddenly appeared in database:
select * from pg_largeobject_metadata
Oid Lomowner
200936761
On 2/1/21 12:07 PM, Andrus wrote:
Hi!
>Large objects exist independent of those. The important part of the
above is lomowner. Use that oid to find the role that owns the
objects here:
/select rolname from pg_roles where oid = 30152 ; //
Not sure what the above is supposed to be doing?
Hi!
>Large objects exist independent of those. The important part of the
above is lomowner. Use that oid to find the role that owns the
objects here:
/select rolname from pg_roles where oid = 30152 ; //
Not sure what the above is supposed to be doing?
I showed the user definition
I
On Mon, 2021-02-01 at 18:32 +0200, Andrus wrote:
> > > Database does not contain large objects.
> > >
> > > pg_dump starts to throw error
> > >
> > > ERROR: permission denied for large object 200936761
> >
> > Did you do the pg
On 2/1/21 9:55 AM, Andrus wrote:
Hi!
>Large objects exist independent of those. The important part of the
above is lomowner. Use that oid to find the role that owns the objects
here:
/select rolname from pg_roles where oid = 30152 ; //
/
returns my role , clusteradmin . I have superuser r
Hi!
>Large objects exist independent of those. The important part of the
above is lomowner. Use that oid to find the role that owns the objects
here:
/select rolname from pg_roles where oid = 30152 ; //
/
returns my role , clusteradmin . I have superuser rights:
CREATE ROLE clusteradmin WI
Hi!
>Well the user that runs the pg_dump needs to have permissions on the
large objects. For more information see below.
How to add permissions to non-superusers for this.?
GRANT command
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT/|loid|/ [, ...]
TO/|r
Hi!
Database does not contain large objects.
pg_dump starts to throw error
ERROR: permission denied for large object 200936761
Did you do the pg_dump as a superuser?
No.
pg_dump needs to be invoked by non-superuser also. It backs up two
schemas, public and firma74 .
-n public -n
On 2/1/21 9:13 AM, Andrus wrote:
Hi!
>Well the user that runs the pg_dump needs to have permissions on the
large objects. For more information see below.
How to add permissions to non-superusers for this.?
GRANT command
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LAR
On 2/1/21 8:32 AM, Andrus wrote:
Hi!
Database does not contain large objects.
pg_dump starts to throw error
ERROR: permission denied for large object 200936761
Did you do the pg_dump as a superuser?
No.
pg_dump needs to be invoked by non-superuser also. It backs up two
schemas, public
On 2/1/21 6:43 AM, Andrus wrote:
Hi!
Database does not contain large objects.
pg_dump starts to throw error
ERROR: permission denied for large object 200936761
Did you do the pg_dump as a superuser?
More below.
Tried
select * from "200936761"
but it returned "relation
Hi!
Database does not contain large objects.
pg_dump starts to throw error
ERROR: permission denied for large object 200936761
Tried
select * from "200936761"
but it returned "relation does not exist"
How to fix this ? How to find which table causes this error ?
Ho
27 matches
Mail list logo
