Re: Is there any good optimization solution to improve the query efficiency?

Hello, In many case a formal writing and usage of with statement could solve the issue. If you need join, use always join: where T_POV2.RSNO = T_CUST.RSNO and T_POV2.KNO = T_CUST.KNO and T_POV2.GSTSEQ = T_CUST.GSTSEQ) this is an inner join. I mean something like this with t_pov2 as ( select T_CUST

Composite type: Primary Key and validation

Hello guys, I've a couple of questions about composite type. Suppose this composite type: CREATE TYPE my_type AS ( user_ts_start My_start_timestamp, user_ts_end My_end_timestamp, db_ts_start My_start_timestamp, db_ts_end My_end_timestamp, audit_record jsonb ); My_start_timestamp

Re: Is there any good optimization solution to improve the query efficiency?

ly to my question. > > I followed your suggestion and rewrote the SQL using Common Table > Expression (CTE). > > Unfortunately, there was no significant improvement in performance. > > > > At 2023-06-05 17:47:25, "Lorusso Domenico" wrote: > > Hello, >

Re: Composite type: Primary Key and validation

Thank's a lot, I'll take care of it. Il giorno lun 5 giu 2023 alle ore 16:18 Ron ha scritto: > On 6/5/23 09:02, Laurenz Albe wrote: > > On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote: > >> I've a couple of questions about composite type. > >>

Re: Composite type: Primary Key and validation

I could use the "table inheritance", but I've to reset all constraints for each table :-( Il giorno lun 5 giu 2023 alle ore 16:02 Laurenz Albe < laurenz.a...@cybertec.at> ha scritto: > On Mon, 2023-06-05 at 11:49 +0200, Lorusso Domenico wrote: > > I've a couple o

Re: Composite type: Primary Key and validation

thank's Adrian, my problem is I've to use CloudSql, so I can't install extensions not verified by google... Il giorno lun 5 giu 2023 alle ore 17:17 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 6/5/23 08:06, Lorusso Domenico wrote: > > Thank's,

Re: Is there any good optimization solution to improve the query efficiency?

UST T_CUST on T_RES.RSNO = T_CUST.RSNO > inner join TBL_POV T_POV on T_POV.CRSNO = T_RES.CRSNO -- why you use this > table? it doesn't seem to be used to extract data. Are you trying to > extract data from T_RES that have at least a record in T_POV? in this case > could work bet

How to manipulate field in New record

Hello, Looking for a global solution I've write e trigger function that as optional parameter (argv[0]) receive the name of a specific parameter. My need is to get the filed from NEW and OLD record manipulate and set back the field with new value. Problem 1: how to get and set this field Problem

Re: How to manipulate field in New record

yes I know, the trigger should be before insert update and delete, and the function decide what manipulate basing on TG_OP Il giorno mer 7 giu 2023 alle ore 15:57 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 6/7/23 05:12, Lorusso Domenico wrote: > > Hello, > >

Re: Question about where to deploy the business logics for data processing

Uhm me need to start form 2 concepts: 1. competence 2. Network lag Competence: usually programmers aren't skilled enough about the architectures and the actual needs of each layer. This is a problem, because often programmers try to do something with what he already know (e.g. perform join

Re: Question about where to deploy the business logics for data processing

ation (partitioning, index and so on). Coming to your last question, where set the logic of data manipulation, again, in this case, minimize the lan traffic could be your main goal, this means logic inside the DB. Il giorno ven 9 giu 2023 alle ore 18:34 Lorusso Domenico < domenico@gmail.com>

Dynamic binding issue

Hello guys, I'm a problem with dynamic sql. I am trying to write a generic function that is able to read and update a table based on some data coming from e previous record. Here the example _sqlStr=format('select * from %1$s.%2$s where (' || array_to_string(_activeRec.pk_columns_list, ',') ||

Re: Dynamic binding issue

Because the function is general and should do the same thing for many different table. Unique constraint, each table must have a field with a specific type Il mar 13 giu 2023, 01:31 Adrian Klaver ha scritto: > On 6/12/23 15:13, Lorusso Domenico wrote: > > Hello guys, > > I&#

foreign keys on multiple parent table

Hello guys, I've many tables representing as many concepts. For each record of each table I need to store extra information (think to audit information, but more complex than a simple text) The relation is 1:N, for each record there could be many audit records. >From programming point of view an

Re: foreign keys on multiple parent table

Thank you Les for the link, it's a very good example, unfortunately my need is more applicative (we need to store user of application, not the on pg, proces who start etc), but for sure I can take advantage of it. Il giorno mar 20 giu 2023 alle ore 23:01 Les ha scritto: > . >> > > >> From progra

Re: foreign keys on multiple parent table

ehm.. I'm not sure I understood correctly :-D in which way do you generate column? Il giorno mer 21 giu 2023 alle ore 09:47 Dominique Devienne < ddevie...@gmail.com> ha scritto: > On Tue, Jun 20, 2023 at 10:47 PM Lorusso Domenico > wrote: > >> Could work, but is ther

Strange behaviour on function

Hello guys, here a simple function CREATE OR REPLACE FUNCTION bind_action( sqlstr text, hrec hstore) RETURNS text LANGUAGE 'plpgsql' COST 100 immutable PARALLEL SAFE AS $BODY$ declare _sqlstr text=sqlstr; _k text; _debug text; begin _debug= '--Start' || _sqlstr; foreach _k in array

Re: Strange behaviour on function

yes!! I solved using quote_nullable on hrec[k]. I was convinced string || NULL --> string but I'm wrong. Thanks!! Il giorno mer 5 lug 2023 alle ore 15:53 Erik Wienhold ha scritto: > > On 05/07/2023 14:23 CEST Lorusso Domenico > wrote: > > > > Hello guy

Re: Strange behaviour on function

Erik, Adrian, The trigger function is very long and complex (and not yet well documented), but bind_action is a normal function callable everywhere, the problem as discovered by Erik was in the null value contained in the hstore. Anyway, when the ecosystem of functions will work correctly I can s

Re: Moving data from huge table slow, min() query on indexed column taking 38s

Hello, this is a standard problem during bulk copy. here some suggestions; for example disable indexes. The main issue is related to index, lock escalation and log writing. In other dbms you should set log off on the table, but postgresql does no

[bug]? insert returning composite type fails

Hello guys, In my db (version 15) I've defined a composite type with some domains CREATE DOMAIN my_feat.audit_record_jsonb_domain AS jsonb NOT NULL; ALTER DOMAIN my_feat.audit_record_jsonb_domain OWNER TO postgres; CREATE DOMAIN my_feat.boolean_true_domain AS boolean DEFAULT true

Re: [bug]? insert returning composite type fails

Adrian come on 🤭 This is a reduced example. The real usecase involves many tables with the bitemporal record However I solved using a record type ... Il ven 7 lug 2023, 01:20 Adrian Klaver ha scritto: > On 7/6/23 14:52, Lorusso Domenico wrote: > > Hello guys, > > In my db (v

How to add function schema in search_path in option definitio

Hello guys, there is a comfortable way to add the schema of the function as in search path? something like create my_schema.function() as $body$...$body$ set search_path to function_schema() || search_path -- Domenico L. per stupire mezz'ora basta un libro di storia, io cercai di imparare la Tr

Re: How to add function schema in search_path in option definitio

ike to use just _returnOfBar=bar(p1,p2,..,pn); Il giorno sab 8 lug 2023 alle ore 17:46 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 7/8/23 08:06, Lorusso Domenico wrote: > > Hello guys, > > there is a comfortable way to add the schema of the function as in >

Re: How to add function schema in search_path in option definitio

Hello Adrian and Peter, yes the set parameter in function definition is also my preferred choice, but I need to add the schema to path, not to substitute the path, this is my problem Il giorno dom 9 lug 2023 alle ore 13:02 Peter J. Holzer ha scritto: > On 2023-07-08 19:00:02 +0200, Loru

Re: Moving data from huge table slow, min() query on indexed column taking 38s

right? There is > no way to COPY from table to table directly? > > > Thanks, > Dimitris > > On Thu, 6 Jul 2023, Lorusso Domenico wrote: > > > Hello, > > this is a standard problem during bulk copy. > > > > here some suggestions; for example disable index

Re: How to add function schema in search_path in option definitio

ha scritto: > On 7/9/23 13:47, Lorusso Domenico wrote: > > Hello Adrian and Peter, > > yes the set parameter in function definition is also my preferred > > choice, but > > > > I need to add the schema to path, not to substitute the path, this is m

Re: Trigger Function question

I've just finish to do the same thing. For my needs, I decided to create a table with the information I need for each view (yes I set trigger on view not on table). anyway, hstore is more performant rather than jsonb but both of them could treat NEW and OLD as hashmap (or associative array) Il g

Re: How to add function schema in search_path in option definitio

l My question doesn't aim just to fix a specific issue, but to learn the best way (if exist) to solve the class of issues (raised by the case) in postgresql. Il giorno mar 11 lug 2023 alle ore 21:41 Adrian Klaver < adrian.kla...@aklaver.com> ha scritto: > On 7/11/23 12:35, Lorusso Dome

Re: How to add function schema in search_path in option definitio

Il giorno mar 11 lug 2023 alle ore 22:51 David G. Johnston < david.g.johns...@gmail.com> ha scritto: > On Sat, Jul 8, 2023 at 10:00 AM Lorusso Domenico > wrote: > >> Hello Adrian, >> I've created a schema to handle some specific features. >> In the schema there

Re: Dynamically accessing columns from a row type in a trigger

Well, some weeks ago, I read an article about that. The more efficient approach (in pgplsql) is to use hstore. With a similar topic, at the end, I created a group of functions that store in an internal table, data structure for each table under the same generic trigger. I also store, primary key.

Schema renaming cascade

Hello guys, I need to rename a schema, including each reference to it (also for functions) I mean: I've 2 schemas called "schema1" and "schema2". In schema1 there are tables, composite types, and functions Functions call other functions in the same schema In schema2 Also in schema2 there are tabl

rollback to savepoint issue

Hello guys, I can't use the savepoint and rollback to savepoint clause. I've found some similar problems around on the web, but I can't catch the good way to proceed. What I'm trying to do is: - compare new set of attribute with older - if some attributes are part of old set and not in the

Array vs Temporary table vs Normal Table + truncate at end

Hello guys, I prepared a function that build a set of records of type (myComplexType). That works, but I've to use this record set in different situations. One of them require to scan multiple times the function results: 1. comparing with another table (myProducteAttributeTable) to determine

Re: Efficient Partitioning Strategies for PostgreSQL Table with KSUID and High Volume

Hello, Increase of access cost is logarithmic when we access by index, so partition often isn't a solution to improve performance, but a solution to solve human difficulties to manage huge amounts of data, to develop expensive parallel jobs and, in some cases, to improve performance for sequential

select from composite type

Hello guys, I'm trying to find out the equivalent behaviour of unnest, when I've got just a composite type. This is the statement is something like that (but more complex): _sqlUpdate text=$$ with s as ( select * from ($1) ) update myView as q set (attribute_fullname, modify_user_id) =(s.attribu

Re: select from composite type

here an example (the actual case in more complex, but the point it's the same) do $$ declare _attribute_list temp1.my_type[]; _attribute temp1.my_type; _r record; begin _attribute_list=array[row(1,'Hello') , row(2,'Goodbye')]; _attribute= row(1,'Doh'); raise notice '%', _attribute_list; fo

Re: select from composite type

ah ehm.. I solved, it was very easy but I believed it should use the from clause... execute 'select ($1).* ' using _attribute into _r; Il giorno mar 6 feb 2024 alle ore 01:01 Lorusso Domenico < domenico@gmail.com> ha scritto: > here an example (the actual case in more com

Migrate schemas

Hello guys, I have 4 schemas with cross references (e.g.: a function refers to a rowtype of a table of another schema, or a table invokes a function). Backup schemas by pgadmin the resulting script doesn't follow the correct order to ensure the object creations. There is a way to tell postgresql

extract ddl to devops pipeline

Hello guys, I need to export the DDL (tables, funcitons views) of some schemas (with cross references) to load them in a pipeline like DevOps ready. Problem: export/backup doesn't care about the appropriate sequence of objet because these stuff will be solved by import phase. So there is a way to

merge with view

Hello guys, Merge isn't work on view; anyone know why? I mean, merge could be performed in many way, but essentially is: - join sets - if matched update or delete - if not matched insert it doesn't seem to be relevant if set is a table or a view. Moreover also "insert + on conflict" doe