Re: update faster way

[Juan Rodrigo Alejandro Burgos Mella]

The only way that I see as plausible to use a subquery, both in the query
and in the setting of the variable, is that the relationship is one to one,
and that there is an index that responds to the predicate

UPDATE table1 t1
SET column_value = (SELECT  FROM table2 t2 WHERE t2.column_relation
= t1.column_relation)
WHERE (colum_relation) IN (SELECT column_relation FROM table2)

PD: the index of being in table2

Atte
JRBM

El sáb, 14 sept 2024 a las 0:22, yudhi s ()
escribió:

> Hello,
> We have to update a column value(from numbers like '123' to codes like
> 'abc' by looking into a reference table data) in a partitioned table with
> billions of rows in it, with each partition having 100's millions rows. As
> we tested for ~30million rows it's taking ~20minutes to update. So if we go
> by this calculation, it's going to take days for updating all the values.
> So my question is
>
> 1) If there is any inbuilt way of running the update query in parallel
> (e.g. using parallel hints etc) to make it run faster?
> 2) should we run each individual partition in a separate session (e.g.
> five partitions will have the updates done at same time from 5 different
> sessions)? And will it have any locking effect or we can just start the
> sessions and let them run without impacting our live transactions?
>
> UPDATE tab_part1
> SET column1 = reftab.code
> FROM reference_tab reftab
> WHERE tab_part1.column1 = subquery.column1;
>
> Regards
> Yudhi
>

Re: Manual query vs trigger during data load

[Juan Rodrigo Alejandro Burgos Mella]

Hello, I find it unlikely that the trigger will work properly, since the
reserved fields of the OLD subset have no value in an INSERT

Atte
JRBN

El vie, 13 de sept de 2024, 04:32, yudhi s 
escribió:

> Hello All,
>
> We are having a table which is going to be inserted with 100's of millions
> of rows each day. And we now want to have a requirement in which we need to
> do some transformation/lookup logic built on top of a few of the input bind
> values , while inserting the data. So I wanted to understand ,is it
> possible to do it along with the INSERT query or is it better to have a
> trigger created for the same?
>
> For. e.g Below is the current Insert query used in the Java code. We want
> to fetch the value for "column2" from a lookup table rather than directly
> inserting as it's coming from the customer side. So I am thinking of a
> trigger like below. But at the same time I also want to compare the
> performance of a normal way of doing the lookup vs having it performed
> using triggers.
>
> So one way i am thinking is first fetching the value of the "column2" from
> reference_tab1 using a separate "select query" in Java code itself,  and
> then passing that to the below insert query, but i think that will increase
> the response time as that will be a separate DB call.
>
> 1)So,  is there a way I can do it directly using the single INSERT query
> itself without additional SELECT query? And then will try to compare that
> with the trigger based approach.
> 2)Additionally , if this decision will impact a batch insert approach.
> i.e. say , in case of trigger , will the batch insert fail because
> trigger will force it to make it row by row?
>
> INSERT INTO tab_part1 (column1, column2, column3, column4, column5,
> part_date)
> VALUES (:v_col1, :v_col2, :v_col3, :v_col3, :v_col4,:v_col5,
> CURRENT_DATE);
>
> CREATE OR REPLACE FUNCTION trg_func_populate_column2() RETURNS TRIGGER AS
> $$
> BEGIN
> -- Fetch reference value and populate column2
> NEW.column2 := (SELECT lookup_key FROM reference_tab1 WHERE lookup_key
> = old.column2);
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> Regards
> Yudhi
>

Re: can stored procedures with computational sql queries improve API performance?

[Juan Rodrigo Alejandro Burgos Mella]

Great tip!!! Thx

El mié, 10 de jul de 2024, 16:17, Ron Johnson 
escribió:

> On Tue, Jul 9, 2024 at 8:58 PM Krishnakant Mane 
> wrote:
>
>> Hello.
>>
>> I have a straight forward question, but I am just trying to analyze the
>> specifics.
>>
>> So I have a set of queries depending on each other in a sequence to
>> compute some results for generating financial report.
>>
>> It involves summing up some amounts from tuns or of rows and also on
>> certain conditions it categorizes the amounts into types (aka Debit
>> Balance, Credit balance etc).
>>
>> There are at least 6 queries in this sequence and apart from 4 input
>> parameters. these queries never change.
>>
>> So will I get any performance benefit by having them in a stored
>> procedure rather than sending the queries from my Python based API?
>
>
> One problem is that the query planner reverts to a generic query plan if
> you execute the same query over and over in a loop in the SP.
>
> That bit us once.  A big SP that had been running "normally" for months
> suddenly went from about 20 minutes to six hours.  The solution (given by
> someone on this list a couple of years ago) was to add "set plan_cache_mode
> = force_custom_plan;" above the call.
>
> That way, the query plan was updated every time.  Performance dropped to
> about 8 minutes IIRC.
>
>

Re: Dll libpq.dll 32 bits

[Juan Rodrigo Alejandro Burgos Mella]

What type of project are you developing at Windev? As a proof of concept,
maybe using libpq.lib we can do something

Atte
JRBM

El jue, 30 may 2024 a las 16:37, José Mello Júnior (<
jose.mello.jun...@gmail.com>) escribió:

> Don't have more?
>
> Em qui., 30 de mai. de 2024, 16:14, Juan Rodrigo Alejandro Burgos Mella <
> rodrigoburgosme...@gmail.com> escreveu:
>
>> postgresql odbc has 32 and 64 bits, and you make a System DSN and that's
>> it
>> I have made implementations for PHP, .Net, C#, Python; even ASP Classic.
>>
>> Atte.
>> JRBM
>>
>> El jue, 30 may 2024 a las 13:27, José Mello Júnior (<
>> jose.mello.jun...@gmail.com>) escribió:
>>
>>> No, I can´t use ODBC in this case, because I only access a new server. I
>>> need the DLL in 32 bits.
>>>
>>> Atte
>>> Mello
>>>
>>> Em qua., 29 de mai. de 2024 às 23:45, Juan Rodrigo Alejandro Burgos
>>> Mella  escreveu:
>>>
>>>> you can use ODBC with Windev, so the best is the Postgresql connector
>>>> https://www.postgresql.org/ftp/odbc/releases/
>>>>
>>>> Atte
>>>> JRBM
>>>>
>>>> El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
>>>> jose.mello.jun...@gmail.com>) escribió:
>>>>
>>>>>
>>>>> Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
>>>>> adrian.kla...@aklaver.com> escreveu:
>>>>>
>>>>>> On 5/29/24 11:18, José Mello Júnior wrote:
>>>>>>
>>>>>> Again reply to list by using Reply All.
>>>>>> Ccing list.
>>>>>> > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.
>>>>>>
>>>>>> Are you talking about this?:
>>>>>>
>>>>>> https://windev.com/pcsoft/index.html
>>>>>>
>>>>>> >
>>>>>> > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
>>>>>> > mailto:adrian.kla...@aklaver.com>>
>>>>>> escreveu:
>>>>>> >
>>>>>> >
>>>>>> >
>>>>>> > On 5/29/24 10:59 AM, José Mello Júnior wrote:
>>>>>> >
>>>>>> > Reply to list also.
>>>>>> > Ccing list.
>>>>>> >
>>>>>> >  > Sorry, for windows
>>>>>> >
>>>>>> > That is the OS, what I am after is what client on Windows do
>>>>>> you need
>>>>>> > the DLL for?
>>>>>> >
>>>>>> >  >
>>>>>> >  >
>>>>>> >  >
>>>>>> >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
>>>>>> >  > mailto:adrian.kla...@aklaver.com
>>>>>> >
>>>>>> > <mailto:adrian.kla...@aklaver.com
>>>>>> > <mailto:adrian.kla...@aklaver.com>>> escreveu:
>>>>>> >  >
>>>>>> >  >
>>>>>> >  >
>>>>>> >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
>>>>>> >  >  > Where do i found this dll for acess postgresql 15?
>>>>>> >  >
>>>>>> >  > Access Postgres using what client?
>>>>>> >  >
>>>>>> >  > --
>>>>>> >  > Adrian Klaver
>>>>>> >  > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>> > <mailto:adrian.kla...@aklaver.com >>>>> adrian.kla...@aklaver.com>>
>>>>>> >  >
>>>>>> >
>>>>>> > --
>>>>>> > Adrian Klaver
>>>>>> > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>>>> >
>>>>>>
>>>>>> --
>>>>>> Adrian Klaver
>>>>>> adrian.kla...@aklaver.com
>>>>>>
>>>>>>
>>>
>>> --
>>> Mello Júnior
>>> 41.3252-3555
>>>
>>

Re: Dll libpq.dll 32 bits

[Juan Rodrigo Alejandro Burgos Mella]

postgresql odbc has 32 and 64 bits, and you make a System DSN and that's it
I have made implementations for PHP, .Net, C#, Python; even ASP Classic.

Atte.
JRBM

El jue, 30 may 2024 a las 13:27, José Mello Júnior (<
jose.mello.jun...@gmail.com>) escribió:

> No, I can´t use ODBC in this case, because I only access a new server. I
> need the DLL in 32 bits.
>
> Atte
> Mello
>
> Em qua., 29 de mai. de 2024 às 23:45, Juan Rodrigo Alejandro Burgos Mella <
> rodrigoburgosme...@gmail.com> escreveu:
>
>> you can use ODBC with Windev, so the best is the Postgresql connector
>> https://www.postgresql.org/ftp/odbc/releases/
>>
>> Atte
>> JRBM
>>
>> El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
>> jose.mello.jun...@gmail.com>) escribió:
>>
>>>
>>> Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
>>> adrian.kla...@aklaver.com> escreveu:
>>>
>>>> On 5/29/24 11:18, José Mello Júnior wrote:
>>>>
>>>> Again reply to list by using Reply All.
>>>> Ccing list.
>>>> > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.
>>>>
>>>> Are you talking about this?:
>>>>
>>>> https://windev.com/pcsoft/index.html
>>>>
>>>> >
>>>> > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
>>>> > mailto:adrian.kla...@aklaver.com>>
>>>> escreveu:
>>>> >
>>>> >
>>>> >
>>>> > On 5/29/24 10:59 AM, José Mello Júnior wrote:
>>>> >
>>>> > Reply to list also.
>>>> > Ccing list.
>>>> >
>>>> >  > Sorry, for windows
>>>> >
>>>> > That is the OS, what I am after is what client on Windows do you
>>>> need
>>>> > the DLL for?
>>>> >
>>>> >  >
>>>> >  >
>>>> >  >
>>>> >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
>>>> >  > mailto:adrian.kla...@aklaver.com>
>>>> > <mailto:adrian.kla...@aklaver.com
>>>> > <mailto:adrian.kla...@aklaver.com>>> escreveu:
>>>> >  >
>>>> >  >
>>>> >  >
>>>> >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
>>>> >  >  > Where do i found this dll for acess postgresql 15?
>>>> >  >
>>>> >  > Access Postgres using what client?
>>>> >  >
>>>> >  > --
>>>> >  > Adrian Klaver
>>>> >  > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>> > <mailto:adrian.kla...@aklaver.com >>> adrian.kla...@aklaver.com>>
>>>> >  >
>>>> >
>>>> > --
>>>> > Adrian Klaver
>>>> > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
>>>> >
>>>>
>>>> --
>>>> Adrian Klaver
>>>> adrian.kla...@aklaver.com
>>>>
>>>>
>
> --
> Mello Júnior
> 41.3252-3555
>

Re: Dll libpq.dll 32 bits

[Juan Rodrigo Alejandro Burgos Mella]

you can use ODBC with Windev, so the best is the Postgresql connector
https://www.postgresql.org/ftp/odbc/releases/

Atte
JRBM

El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
jose.mello.jun...@gmail.com>) escribió:

>
> Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
> adrian.kla...@aklaver.com> escreveu:
>
>> On 5/29/24 11:18, José Mello Júnior wrote:
>>
>> Again reply to list by using Reply All.
>> Ccing list.
>> > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.
>>
>> Are you talking about this?:
>>
>> https://windev.com/pcsoft/index.html
>>
>> >
>> > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
>> > mailto:adrian.kla...@aklaver.com>>
>> escreveu:
>> >
>> >
>> >
>> > On 5/29/24 10:59 AM, José Mello Júnior wrote:
>> >
>> > Reply to list also.
>> > Ccing list.
>> >
>> >  > Sorry, for windows
>> >
>> > That is the OS, what I am after is what client on Windows do you
>> need
>> > the DLL for?
>> >
>> >  >
>> >  >
>> >  >
>> >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
>> >  > mailto:adrian.kla...@aklaver.com>
>> > > > >> escreveu:
>> >  >
>> >  >
>> >  >
>> >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
>> >  >  > Where do i found this dll for acess postgresql 15?
>> >  >
>> >  > Access Postgres using what client?
>> >  >
>> >  > --
>> >  > Adrian Klaver
>> >  > adrian.kla...@aklaver.com 
>> > > >>
>> >  >
>> >
>> > --
>> > Adrian Klaver
>> > adrian.kla...@aklaver.com 
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>

Re: Controlling resource utilization

[Juan Rodrigo Alejandro Burgos Mella]

In postgreSQL, that can be done at a session level, or at a general level
(in the postgresql.conf configuration file)

Atte
JRBM

El mar, 16 abr 2024 a las 15:18, yudhi s ()
escribió:

>
>
> On Wed, 17 Apr, 2024, 1:32 am Juan Rodrigo Alejandro Burgos Mella, <
> rodrigoburgosme...@gmail.com> wrote:
>
>> Yes sir
>>
>> SET statement_timeout TO ''
>>
>> Atte
>> JRBM
>>
>> El mar, 16 abr 2024 a las 14:46, yudhi s ()
>> escribió:
>>
>>> Hi ,
>>> We want to have controls around the DB resource utilization by the adhoc
>>> user queries, so that it won't impact the application queries negatively.
>>> Its RDS postgresql database version 15.4.
>>>
>>> Saw one parameter as statement_timeout which restricts the queries to
>>> not run after a certain time duration and queries will be automatically
>>> killed/cancelled. However, I don't see any other options to set this at
>>> user level, rather it's getting set for all or at session level. So I want
>>> to know if there exists, anyway to control the database resource
>>> utilization specific to users?
>>>
>>> Regards
>>> Yudhi
>>>
>>
>
> This will set the timeout at session level. However, We want to
> understand, if it can be done at user/role level, so that any such adhoc
> user queries can be auto killed or cancelled after the set time.
>
>>
>>>

Re: Controlling resource utilization

[Juan Rodrigo Alejandro Burgos Mella]

Yes sir

SET statement_timeout TO ''

Atte
JRBM

El mar, 16 abr 2024 a las 14:46, yudhi s ()
escribió:

> Hi ,
> We want to have controls around the DB resource utilization by the adhoc
> user queries, so that it won't impact the application queries negatively.
> Its RDS postgresql database version 15.4.
>
> Saw one parameter as statement_timeout which restricts the queries to not
> run after a certain time duration and queries will be automatically
> killed/cancelled. However, I don't see any other options to set this at
> user level, rather it's getting set for all or at session level. So I want
> to know if there exists, anyway to control the database resource
> utilization specific to users?
>
> Regards
> Yudhi
>
>

Re: Trigger to Count Number of Logical Replication Table Changes.

[Juan Rodrigo Alejandro Burgos Mella]

Hi
The only thing that can be established is that if the record does not
exist, the trigger will not be updating anything in the table
"tst.time_audit_tbl" for the condition "table_name =
CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME)"

Maybe, checking before the UPDATE, if the record does not exist, add it.

IF NOT EXISTS (SELECT 1 FROM tst.time_audit_tbl WHERE table_name =
CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME))
THEN
INSERT INTO FROM tst.time_audit_tbl (table_name) VALUES
(CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME));
END IF

Atte.
JRBM



El jue, 16 nov 2023 a las 14:19, Avi Weinberg () escribió:

> I'm using Postgres (13 and 15) logical replication to sync data from two
> servers. I would like to have an update counter whenever data is changed.
> The counter can be incremented by 1 even if multiple rows are updated, but
> it is also ok to be incremented the counter by the number of rows updated
> (but it seems less efficient to me).
> I need the counter to increase after initial sync as well as after regular
> logical replication sync.
>
> Triggers not to work without ENABLE ALWAYS.
>
> In addition, If I try trigger that is "FOR EACH STATEMENT" it works only
> for initial sync and not for regular logical replication sync.
>
> Having per row set_time_trig  trigger takes about 1 minute when updating
> 50k rows in one transaction (all I need is to increase update_count by 1,
> why spend 1 minute for it) . How can I improve this?
>
> CREATE TABLE IF NOT EXISTS tst.t2
>
> (
>
> id bigint NOT NULL,
>
> c1 int,
>
> CONSTRAINT pk_t2 PRIMARY KEY (id)
>
> );
>
>
>
> CREATE TABLE IF NOT EXISTS tst.time_audit_tbl
>
> (
>
> table_name character varying(63) COLLATE pg_catalog."default" NOT NULL,
>
> update_count integer DEFAULT 0,
>
> CONSTRAINT updated_time_audit_unique UNIQUE (table_name)
>
> );
>
>
>
>
>
> CREATE FUNCTION tst.set_time() RETURNS trigger
>
> LANGUAGE plpgsql SECURITY DEFINER
>
> AS $$
>
> DECLARE
>
>   updated_count int;
>
> BEGIN
>
> UPDATE tst.time_audit_tbl SET update_count = update_count + 1
> WHERE table_name = CONCAT(TG_TABLE_SCHEMA, '.', TG_TABLE_NAME);
>
> GET DIAGNOSTICS updated_count = ROW_COUNT;
>
> IF updated_count = 0 THEN
>
>   RAISE EXCEPTION 'set_updated_time().  Table not found %.%',
> TG_TABLE_SCHEMA, TG_TABLE_NAME;
>
> END IF;
>
>
>
> RETURN coalesce(NEW, OLD);
>
> END;
>
> $$;
>
>
>
>
>
> CREATE  TRIGGER set_time_trig
>
> AFTER INSERT OR DELETE OR UPDATE
>
> ON tst.t2
>
> FOR EACH ROW
>
> EXECUTE FUNCTION tst.set_time();
>
>
>
> ALTER TABLE tst.t2 ENABLE ALWAYS TRIGGER set_time_trig;
>
>
> IMPORTANT - This email and any attachments is intended for the above named
> addressee(s), and may contain information which is confidential or
> privileged. If you are not the intended recipient, please inform the sender
> immediately and delete this email: you should not copy or use this e-mail
> for any purpose nor disclose its contents to any person.
>

Re: My question about the transaction

[Juan Rodrigo Alejandro Burgos Mella]

By default, psql is configured with autocommit on.
Use another administrator, like phpadmin, and configure the connection with
autocommit off.

Atte
JRBM

El jue, 19 oct 2023 a las 11:49, Wen Yi () escribió:

> Hi community,
> I am learning the transaction of the postgresql, and I try to test using
> these:
>
> ##
>
> terminal 1:
>
> postgres=# select * from t;
>  number
> 
>   1
> (1 row)
>
> postgres=# update t set number = 2;
> UPDATE 1
> postgres=# select * from t;
>  number
> 
>   2
> (1 row)
>
> postgres=# select * from t;
>  number
> 
>   2
> (1 row)
>
> postgres=#
>
> ##
>
> terminal 2:
>
> postgres=# create table t (number integer);
> CREATE TABLE
> postgres=# insert into t values (1);
> INSERT 0 1
> postgres=# begin;
> BEGIN
> postgres=*# insert into t values (2);
> INSERT 0 1
> postgres=*# select * from t;
>  number
> 
>   2
>   2
> (2 rows)
>
> postgres=*# rollback;
> ROLLBACK
>
> My question is, in my view, the transaction model should make sure that
> when one on-process transaction don't commit itself, the data on this
> transaction shouldn't can be modified by other transaction(I the
> single-statement also be treated as a simple transaction), but why the
> update works?(I think terminal 1 will block until the terminal 2's
> transaction commit or rollback).
> Can someone share you opinion to me?
> Thanks in advance!
>
> Yours,
> Wen Yi
>
>
>

ECPG Semantic Analysis

[Juan Rodrigo Alejandro Burgos Mella]

Hi
I have a modified version of ECPG, to which I gave the ability to do
semantic analysis of SQL statements. Where can you share it or with whom
can I discuss it?

Atte.
JRBM