Re: [Beginner Question]What's the use of ResTarget?
On 2023-Jul-02, Wen Yi wrote: > Hi community > When I read the Internals document (41.1. The Query Tree), > the introduction of the 'the result relation' confuse me. There are "result relations" in commands that modify a relation, such as insert or update. The result relation is where the new tuples are inserted, or where the modified tuples are. > in my original understand, I guess the use of ResTarget is to store > the columns of the result, like: > > SELECT row_1, row_2 FROM table; Yeah. Those aren't necessarily in the result relation, because (as the doc says) a SELECT does not even *have* a result relation. -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/ "Once again, thank you and all of the developers for your hard work on PostgreSQL. This is by far the most pleasant management experience of any database I've worked on." (Dan Harris) http://archives.postgresql.org/pgsql-performance/2006-04/msg00247.php
Re: How to add function schema in search_path in option definitio
On 2023-07-08 19:00:02 +0200, Lorusso Domenico wrote: > I've created a schema to handle some specific features. > In the schema there are a couple of tables used by many functions (more than > 20). > In other words, I've created a schema as a package (as suggested in many > points). So this is one specific schema with a specific name and you don't need this to work in any schema the function happens to be created in? Then I think setting the search path as part of the function definition (as already hinted at by Adrian) may be the easiest solution: create function my_schema.foo (...) returns ... set search_path to my_schema, public as $$ ... $$; You could also do something like: set search_path to my_schema, public; create function foo (...) returns ... set search_path from current as $$ ... $$; 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
now() literal in pg_input_is_valid
Hi should I expect select pg_input_is_valid($$[now(), infinity)$$,'tstzrange'); returns true?
Re: pgbouncer best practices
Thanks for the tips. So far, I am very happy with PGbouncer. Just increased number of db connections. Great piece of software! Is there a way to see how many queued connections there are? Looking at the stats I can't seem to figure that out. On Sat, Jul 8, 2023 at 9:46 AM Ben Chobot wrote: > Rita wrote on 7/8/23 4:27 AM: > > I am not sure if it allows transaction pooling. > > > Well, take the time to figure it out. With transaction pooling enabled, > you can get away with a much lower number of server connections. For > example, our application regularly has thousands of clients connected to > pgbouncer and is quite happy with ~30 server connections (roughly the core > count of the db server). If we couldn't use transaction pooling we'd be > fighting with how to keep those thousands of connections from wasting a > bunch of resources on our db. > > https://www.pgbouncer.org/features.html > -- --- Get your facts first, then you can distort them as you please.--
Re: now() literal in pg_input_is_valid
jian he writes: > should I expect > select pg_input_is_valid($$[now(), infinity)$$,'tstzrange'); > returns true? No. "now()" is not a literal, it's a function call. regards, tom lane
Problematic enforcement of "ERROR: functions in index predicate must be marked IMMUTABLE"
Hi, If you attempt to create an index based on function that is not IMMUTABLE you will get an exception "ERROR: functions in index predicate must be marked IMMUTABLE". However, if you created the index when the function was IMMUTABLE, but later on you updated the function and mistakenly removed the IMMUTABLE key, you will not get any error to alert you that there is an index based on this function and it should remain IMMUTABLE. I suggest triggering error message also when updating a function that is used by index if it is no longer IMMUTABLE Avi 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: 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, Lorusso Domenico wrote: > > I've created a schema to handle some specific features. > > In the schema there are a couple of tables used by many functions (more > than > > 20). > > In other words, I've created a schema as a package (as suggested in many > > points). > > So this is one specific schema with a specific name and you don't need > this to work in any schema the function happens to be created in? > > Then I think setting the search path as part of the function definition > (as already hinted at by Adrian) may be the easiest solution: > > create function my_schema.foo (...) > returns ... > set search_path to my_schema, public > as $$ > ... > $$; > > You could also do something like: > > set search_path to my_schema, public; > create function foo (...) > returns ... > set search_path from current > as $$ > ... > $$; > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" > -- Domenico L. per stupire mezz'ora basta un libro di storia, io cercai di imparare la Treccani a memoria... [F.d.A.]
Re: How to add function schema in search_path in option definitio
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 my problem This post: https://www.postgresql.org/message-id/9f3db764-804e-7154-3f8c-f5f19628178e%40aklaver.com showed a way to do that. -- Adrian Klaver adrian.kla...@aklaver.com
INSERT UNIQUE row?
Hi, Trying to figure out how to insert new property addresses into an existing table. Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to each column won't work in such a case since there are multiple properties * on the same street * in the same town * with the same number on different streets * etc... Each row is only unique as an entity; but the columns are not... Is this covered in the docs? Thanks, Pierre
Re: INSERT UNIQUE row?
A UNIQUE index can have any number of columns, so you can create an index with all of the appropriate columns listed. This is different from having a UNIQUE index on each individual column. In the former case, all of the columns together must be unique; in the latter case, as you mention, each index is evaluated separately. > On Jul 9, 2023, at 15:58, p...@pfortin.com wrote: > > Hi, > > Trying to figure out how to insert new property addresses into an > existing table. > > Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to > each column won't work in such a case since there are multiple properties > * on the same street > * in the same town > * with the same number on different streets > * etc... > > Each row is only unique as an entity; but the columns are not... > > Is this covered in the docs? > > Thanks, > Pierre > > >
Re: INSERT UNIQUE row?
On Sun, Jul 9, 2023 at 3:58 PM wrote: > Hi, > > Trying to figure out how to insert new property addresses into an > existing table. > > Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to > each column won't work in such a case since there are multiple properties > * on the same street > * in the same town > * with the same number on different streets > * etc... > > Each row is only unique as an entity; but the columns are not... > > Is this covered in the docs? > > Yes, the mechanics of defining multi-column unique constraints on tables is covered in the docs. This seems like a waste of space though. That is a lot of text data (though I suppose not as bad so long as you store integer foreign keys for many of the columns) to put into an index that isn't really even going to catch typos and other malformed data situations while preventing something that is basically impossible to encounter in real life. Especially if you also have separate individual indexes to make searching for specific subset of the database faster (i.e., everything in a state). David J.
Re: INSERT UNIQUE row?
On 7/9/23 15:58, p...@pfortin.com wrote: Hi, Trying to figure out how to insert new property addresses into an existing table. Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to each column won't work in such a case since there are multiple properties * on the same street * in the same town * with the same number on different streets * etc... Does the locality you are in have something like the Property ID# and/or Parcel # / Geo ID shown here: https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0 Each row is only unique as an entity; but the columns are not... Is this covered in the docs? Thanks, Pierre -- Adrian Klaver adrian.kla...@aklaver.com
Re: INSERT UNIQUE row?
On 7/9/23 17:04, Adrian Klaver wrote: On 7/9/23 15:58, p...@pfortin.com wrote: Hi, Trying to figure out how to insert new property addresses into an existing table. Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to each column won't work in such a case since there are multiple properties * on the same street * in the same town * with the same number on different streets * etc... Does the locality you are in have something like the Property ID# and/or Parcel # / Geo ID shown here: https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0 Should have added, can you create your own surrogate PK to fill the same role? Each row is only unique as an entity; but the columns are not... Is this covered in the docs? Thanks, Pierre -- Adrian Klaver adrian.kla...@aklaver.com
Re: INSERT UNIQUE row?
On Sun, 9 Jul 2023 17:04:03 -0700 Adrian Klaver wrote: >On 7/9/23 15:58, p...@pfortin.com wrote: >> Hi, >> >> Trying to figure out how to insert new property addresses into an >> existing table. >> >> Can a UNIQUE constraint be applied to an entire row? Adding UNIQUE to >> each column won't work in such a case since there are multiple properties >> * on the same street >> * in the same town >> * with the same number on different streets >> * etc... > >Does the locality you are in have something like the Property ID# and/or >Parcel # / Geo ID shown here: > >https://property.whatcomcounty.us/propertyaccess/PropertySearch.aspx?cid=0 Thanks! Just getting started on this issue and this made me realize my current data source may not be the best... Much appreciated! >> >> Each row is only unique as an entity; but the columns are not... >> >> Is this covered in the docs? >> >> Thanks, >> Pierre >> >> >> >
Re: INSERT UNIQUE row?
On Sun, 9 Jul 2023 16:42:15 -0700 David G. Johnston wrote: >Yes, the mechanics of defining multi-column unique constraints on tables is >covered in the docs. Good to know I'm not searching in vain...
