Re: how can I fix my accent issues?

[Laurenz Albe]

On Fri, 2023-12-08 at 23:58 -0500, Igniris Valdivia Baez wrote:
> hello, I have an ETL process collecting data from a postgresql
> database and xls files and inserting in a postgresql database that
> process occurs great in a local DB in postgres 14 with UTF8
> codification and Spanish_Cuba.1952 collation but when I execute that
> process in dev which is in postgres 15 and UTF8 with collation
> en_US.utf8 the words with accents and ñ looks like an interrogation
> symbol, what can I do to fix this?

If the data you are sending are encoded in WINDOWS-1252 (I assume that
"1952" is just a typo), you should set the client encoding to WIN1252,
so that PostgreSQL knows how to convert the data correctly.

You can do that in several ways; the simplest might be to set the
environment variable PGCLIENTENCODING to WIN1252.

Yours,
Laurenz Albe

how can I fix my accent issues?

[Igniris Valdivia Baez]

hello, I have an ETL process collecting data from a postgresql
database and xls files and inserting in a postgresql database that
process occurs great in a local DB in postgres 14 with UTF8
codification and Spanish_Cuba.1952 collation but when I execute that
process in dev which is in postgres 15 and UTF8 with collation
en_US.utf8 the words with accents and ñ looks like an interrogation
symbol, what can I do to fix this?
thanks in advance

Re: Inserting into foreign table with sequences and default values

[peter.boris...@kartographia.com]

Correction: the trigger on the remote table isn't going to work. I was trying 
so many different things today that I confused myself. Sorry...
 
On the bright side the remote sequence works great and I can insert records 
from the my database to the remote database now which is a step forward.
 
I'll report back if I make any additional progress.

Re: Inserting into foreign table with sequences and default values

[peter.boris...@kartographia.com]

Thanks Tom,
   Your reply helped point me in the right direction. With a little trial and 
error I came up with a hack to solve my issue.
 
First off, I create a shared sequence for the user_id with a technique 
described here:
[ 
https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/
 ]( 
https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/
 )
 
This involved creating a sequence on the remote server and a view of the 
sequence on the remote server. I also updated the "test" table on the remote 
server to populate the "id" column using a the sequence via a trigger.
 
On the local server, I created a foreign table that references the view on the 
remote server and a function that returns a sequence value from the foreign 
table.
 
Finally, on the local server I created a trigger on the remote "test" table 
that was imported earlier via the "IMPORT FOREIGN SCHEMA" command.
 
It's all a little hacky but it works :-)
 
Full steps below for anyone that's interested (sorry if it gets mangled via 
email).
 
Thanks Again,
Peter
 
 

On Remote:


CREATE SEQUENCE user_id;

CREATE VIEW user_id_view AS SELECT nextval('user_id') as a;

CREATE FUNCTION user_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_view;' LANGUAGE SQL;


CREATE TABLE APP.TEST (
ID bigint NOT NULL,
FIRST_NAME text,
LAST_NAME text,
STATUS integer NOT NULL DEFAULT 1,
CONSTRAINT PK_USER PRIMARY KEY (ID)
);

 CREATE OR REPLACE FUNCTION test_function()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id := case when New.id IS NULL then user_id_nextval() else New.id end;
   New.status := case when New.status IS NULL then 1 else New.status end;
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;


 CREATE TRIGGER test_trigger
 BEFORE INSERT
 ON app.test
 FOR EACH ROW
 EXECUTE PROCEDURE test_function();



On Local



CREATE FOREIGN TABLE user_id_foreign_table (a bigint)
SERVER culmen OPTIONS (table_name 'user_id_view');

CREATE FUNCTION user_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_foreign_table;' LANGUAGE SQL;


IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)
FROM SERVER culmen INTO public;


 CREATE OR REPLACE FUNCTION test_function()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id := case when New.id IS NULL then user_id_nextval() else New.id end;
   New.status := case when New.status IS NULL then 1 else New.status end;
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;


 CREATE TRIGGER test_trigger
 BEFORE INSERT
 ON test
 FOR EACH ROW
 EXECUTE PROCEDURE test_function();



 INSERT INTO test(first_name) VALUES ('Bob');


 
 
 

Re: Store PDF files in PostgreDB

[Peter J. Holzer]

On 2023-12-06 09:43:27 -0500, Ron Johnson wrote:
> On Wed, Dec 6, 2023 at 9:39 AM Priyadharshini Vellaisamy 
>  > wrote:
> Please let m know can we store PDF files in PostgreDB ?
> 
> 
> You can store anything up to 1GB in Postgresql using data type bytea.
>  
> 
> If so, can we retrieve it effectively?
> 
> 
> Effectively?
> 
> (We've been storing images in PG for 11 years.)

They probably meant "efficiently".

The way large data is stored in PostgreSQL adds quite a bit of overhead
compared to a plain file in a file system. Plus you also have to deal
with the socket connection (unix or TCP) to the database.

That said, on my laptop (which is several years old and not very fast,
and I'm using Python, which isn't ideal for that job either) I can get
PDFs at somewhere between 47 MB/s and 77 MB/s. Which may or may not be
fast enough.

Another measure of "efficiency" might be how easy it is to use. Here,
bytea fields are very nice: They act just like varchar fields, no
special functions necessary.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Inserting into foreign table with sequences and default values

[Tom Lane]

"peter.boris...@kartographia.com"  writes:
> I guess the FDW is not "seeing" the default value for status and the 
> bigserial sequence for the id column. Is there anyway around this?

The idea is that you should attach default clauses to the foreign
table definition.  A remote serial column is a hard case for that,
though, since you don't have ready access to the remote sequence.
Because of that and some related examples, we don't currently
expect that IMPORT FOREIGN SCHEMA should import the defaults that
exist on the remote server.

Using a view or trigger on the remote side is currently the best
way around that for cases where you can't set up a suitable
default on the local table.

regards, tom lane

Re: running \copy through perl dbi ?

[Vincent Veyron]

On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier  wrote:
> 
> I'm trying to run a PG client side "\copy" command from a perl script.  I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR:  syntax error at or near "\"

Hi David,

This works for me :

#create file
my $sql = qq {\\copy ( $sub_query ) to '$location' with  null as '' 
delimiter ';' csv header } ;

my $db_name = 'xx' ;

my @args = ( 'psql', '-c', $sql, $db_name ) ;

system( @args ) == 0 or die "Bad copy: $?" ;


-- 

Bien à vous, Vincent Veyron

https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres 
d'assurance

Inserting into foreign table with sequences and default values

[peter.boris...@kartographia.com]

Hello,
   I have a few questions inserting data using Foreign Data Wrappers (FDW). 
Consider this simple example.
 
On PostgreSQL Database A (remote):

CREATE TABLE APP.TEST (
ID BIGSERIAL NOT NULL,
FIRST_NAME text,
LAST_NAME text,
STATUS integer NOT NULL DEFAULT 1,
CONSTRAINT PK_USER PRIMARY KEY (ID)
);
 
When I connect directly to this database I can insert without any issues. 
Example:

insert into app.test(first_name) values('Peter');
INSERT 0 1
---
 
On PostgreSQL Database B (local):

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw ...
IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)
FROM SERVER remote_server INTO public;
---
 
When I try to do a simple insert into the test table on database B I get an 
error:
 
insert into test(first_name) values('Mark');
ERROR:  null value in column "id" of relation "test" violates not-null 
constraint
 
If I manually set the id, I get another error
 
insert into "user"(id, first_name) values(2, 'Mark');
ERROR:  null value in column "status" of relation "test" violates not-null 
constraint
 
Only after I set both the id and status fields can I successfully insert.
 
insert into test(id, first_name, status) values(2, 'Mark', 1);
INSERT 0 1
 
I guess the FDW is not "seeing" the default value for status and the bigserial 
sequence for the id column. Is there anyway around this? Is there an option I 
missed when I called IMPORT FOREIGN SCHEMA? Or is there a hack using views or 
triggers?
 
Thanks in advance,
Peter
 
 
 
 

Re: running \copy through perl dbi ?

[David G. Johnston]

On Fri, Dec 8, 2023 at 9:01 AM Adrian Klaver 
wrote:

> On 12/8/23 07:45, David Gauthier wrote:
> > Hi:
> >
> > I'm trying to run a PG client side "\copy" command from a perl script.
> > I tried using $dbh->do("\\copy ...") but it barffed when it saw the
> '\'...
> > ERROR:  syntax error at or near "\"
> >
> > I can do this with a command line approach, attaching to the DB  then
> > run using...
> > -c "\copy ...".  But I was wondering if there is a more elegant way to
> > do this, maybe some sort of legal SQL command that Perl/DBI would
> > swallow ???
>
> Per David Johnston post:
>
> https://metacpan.org/pod/DBD::Pg#COPY-support
>
>
Right.  Since the copy api isn't really a standard thing in the SQL world
you cannot usually use generic database APIs, you need to use
driver-specific interfaces.

David J.

Re: running \copy through perl dbi ?

[Adrian Klaver]

On 12/8/23 07:45, David Gauthier wrote:

Hi:

I'm trying to run a PG client side "\copy" command from a perl script.  
I tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...

ERROR:  syntax error at or near "\"

I can do this with a command line approach, attaching to the DB  then 
run using...
-c "\copy ...".  But I was wondering if there is a more elegant way to 
do this, maybe some sort of legal SQL command that Perl/DBI would 
swallow ???


Per David Johnston post:

https://metacpan.org/pod/DBD::Pg#COPY-support



Thanks in Advance !



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: running \copy through perl dbi ?

[David G. Johnston]

On Fri, Dec 8, 2023 at 8:45 AM David Gauthier  wrote:

>
> I'm trying to run a PG client side "\copy" command from a perl script.  I
> tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
> ERROR:  syntax error at or near "\"
>

More accurately it is the psql application's meta-command.  There is no
such thing as a generic client side command.

>
> I can do this with a command line approach, attaching to the DB  then run
> using...
> -c "\copy ...".  But I was wondering if there is a more elegant way to do
> this, maybe some sort of legal SQL command that Perl/DBI would swallow ???
>
>
The SQL command is just "COPY".  Search for how your driver exposes working
with that command/protocol.

David J.

running \copy through perl dbi ?

[David Gauthier]

Hi:

I'm trying to run a PG client side "\copy" command from a perl script.  I
tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
ERROR:  syntax error at or near "\"

I can do this with a command line approach, attaching to the DB  then run
using...
-c "\copy ...".  But I was wondering if there is a more elegant way to do
this, maybe some sort of legal SQL command that Perl/DBI would swallow ???

Thanks in Advance !

Re: Invalid Parent Index Issue Will Not Resolve Despite Multiple Attempts To Fix

[Noel Jones]

Thank you for your response. I forgot to include it but we did check for
that prior to our submission. We used this query to see how many indexes
were related to the parent index via the inherits table:

SELECT
count(inh.inhrelid)
FROM pg_class c
inner join pg_inherits inh on c.oid = inh.inhparent
WHERE c.oid = broken_parent_index_oid

This query returned a value equal to the number of partitions which
implies that all of the indexes on the partitions are attached to the
parent index that is showing up as broken.


As an additional check this morning I also confirmed that the indexes
were of the same definition and that one of the others (date, not_id)
did not somehow get attached to this parent index using this:

SELECT
par.relname parent_index,
chi.relname child_index,
substring(pg_get_indexdef(par.oid), '.* btree \((.*)\)') as parent_index_cols,
substring(pg_get_indexdef(chi.oid), '.* btree \((.*)\)') as child_index_cols,
par.oid parent_index_oid,
inh.inhparent inherit_parent_oid,
inh.inhrelid inh_child_oid,
ind.indexrelid child_index_oid
FROM pg_inherits inh
join pg_index ind on inh.inhrelid = ind.indexrelid
join pg_class chi on ind.indexrelid = chi.oid
join pg_class par on inh.inhparent = par.oid
WHERE inh.inhparent = broken_parent_index_oid;

>From this I'm seeing that the columns in the definitions are matching
between the two in addition to being attached.


On Thu, Dec 7, 2023 at 10:26 PM Laurenz Albe 
wrote:

>
>  This is an EXTERNAL EMAIL. STOP. THINK before you CLICK links or OPEN
> attachments.
>
> __
> On Thu, 2023-12-07 at 16:38 -0600, Noel Jones wrote:
> > We have been utilizing partitioned tables with indexes. We've recently
> had an issue
> > where the parent table's index (id, date) became invalid
> (indisvalid=FALSE,
> > indisready=FALSE in pg_index). For reference the parent table is
> partitioned on a
> > date field within the table.
>
> Indexes don't become invalid just so.  I wonder what happened.
>
> > We attempted to fix the issue by doing the following:
> >
> > ALTER TABLE table_parent DETACH PARTITION table_badpartition;
> > DROP INDEX brokenchildindex;
> > CREATE INDEX newchildindex on table_badpartition using btree (id, date);
> > ALTER TABLE table_parent ATTACH PARTITION table_badpartition
> > FOR VALUES FROM (date) TO (date+1);
> >
> > This did not fix the issue  so we attempted an alternate fix:
> >
> > begin;
> > set role readwrite;
> > ALTER TABLE table_parent DETACH PARTITION table_badpartition;
> > ALTER TABLE table_badpartition RENAME TO table_badpartition_detached;
> > CREATE TABLE table_badpartition PARTITION OF table_parent
> > FOR VALUES FROM (date) TO (date+1);
> > ALTER TABLE table_badpartitioneplica identity full;
> > INSERT INTO table_badpartition (id, date, ...)
> > SELECT id, date, ... from table_badpartition_detached;
> > commit;
> >
> > This new table was created with the correct columns, the accurate data,
> and the
> > correct indices  however the parent index is still listed with
> indisvalid = FALSE
> > and indisready = FALSE.
>
> You forgot to ALTER INDEX ... ATTACH PARTITION to turn the invalid index
> on the
> partitioned table into a valid index.
>
> Yours,
> Laurenz Albe
>


-- 
*Noel Parker*
Data Engineer
Pronouns: She/Her, They/Them
*apexfintechsolutions.com *

Re: Disable script execution in server level when updating via grids

[Filip Sedlak]

I'd use different users or roles for that - one with more restricted 
rights. I'm not sure how to do it in your specific client.


In general, your user can be granted be granted read-only access and a 
more powerful role to which it can switch explicitly.


mydb=> CREATE TABLE x (id INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE x (id INT);
 ^
mydb=> SET ROLE powerful;
SET
mydb=> CREATE TABLE x (id INT);
CREATE TABLE

In PostgreSQL up to 15, you have to set "NOINHERIT" to your user so you 
don't automatically get all the privileges on login. In 16, you can set 
INHERIT FALSE when granting the role.


--
Filip Sedlák