Re: A question about rules
On 1/19/20 2:32 AM, stan wrote: Please post to list also. Ccing list. On Sat, Jan 18, 2020 at 08:56:06AM -0800, Adrian Klaver wrote: On 1/18/20 8:53 AM, stan wrote: So, I just discovered the rules system. As I understand it, it can be used to rewrite queries before they are passed to the query processor. I was wondering if I could use this to resolve a long standing frustration of mine. I often need to declare a column as NON NULL, and create a trigger/function to auto populate it with something more complex than a simple sequence. This need arises fairly often for me, and I would like to come up with a good way to implement this. Stay away from rules, they will only drive you to distraction. Stick with triggers they are a lot easier to understand and implement. I use triggers quite a bit. My issue is that if I have a column defined as NOT NULL, and assign a trigger that fires a function to auto populate this column if the user (input form) does not. The NOT NULL constraint is checked BEFORE the trigger can fire, which means that I cannot properly provide data integrity. I don't know that this changes with rules. In other words I believe constraints are checked first with either rules/triggers. Someone else will need to confirm this. A work around is to supply a dummy DEFAULT for the the NOT NULL column e.g. for a varchar column the string 'NOT NULL'. Test for this in the trigger and take the appropriate action. Or accept the reality which is, you are allowing NULL input to the column and take away the NOT NULL constraint and look for NULL or empty string input and take the appropriate action. -- Adrian Klaver adrian.kla...@aklaver.com
testing post through gmane.io
gmane.org is now gmane.io hope this works
Re: Are PostgreSQL functions that return sets or tables evaluated lazily or eagerly?
Love to see "return next" work like python yield! Anyone working on that? On Sun, Jan 5, 2020, 5:46 PM Tom Lane wrote: > Gerald Britton writes: > > Back to where I started in my top post: I became interested in this due > to > > the doc note on returning a cursor and that it can be an efficient way to > > handle large result sets. I suppose that implies lazy evaluation. Does > > that mean that if I need plpgsql for a function for he language's power > yet > > want the results to be returned lazily, a cursor is the (only?) way to > go? > > Nope. The docs' reference to a cursor only suggests that if you can > express the function's result as a single SQL query, then opening a > cursor for that query and returning the cursor name will work. But > if you need plpgsql to express the computation, that's not a terribly > helpful suggestion. > > If you'd like to see some actual movement on the missing feature about > lazy evaluation in FROM, you could help test/review the pending patch > about it: > > https://commitfest.postgresql.org/26/2372/ > > However, that still is only half of the problem, because you also need > a PL that is prepared to cooperate, which I don't believe plpgsql is. > I think (might be wrong) that a plpython function using "yield" can > be made to compute its results lazily. > > regards, tom lane >
Re: postgresql commands(psql,createdb,dropdb) are not working from shell script
Hi Ron, I have checked on my Jenkins server.bashrc and. bashprofiles filed of root and Jenkins users but didn't find anything about postgresql path. It might be the reason that Jenkins and Postgresql services are running in two separate Linux instances. >From the Linux server where JENKINS installed, the job is running successfully to take the backup of our production server where postgresql is running on the separate RHEL7 instance after running psql, and pg_dump commands. But the issue is that the jenkins job is failing when we tried to perform the restore activity from prod to stage environments. Do you have any idea where to check the locations where postgresql path in Linux to compare the same prod and stage environments. Thanks and Regards Ramesh Penuballi On Fri, 17 Jan, 2020, 18:52 Ramesh Penuballi, wrote: > Thanks once again for sharing the info. > Let me check and get back to you. > > Thanks and Regards > Ramesh Penuballi > > On Fri, 17 Jan, 2020, 18:43 Ron, wrote: > >> The Jenkins server needs to be able to find the Unix executables dropdb, >> createdb and psql. >> >> I don't know *anything* about Jenkins, or even whether or not Jenkins >> runs on the same box as Postgres, but there's *some* shell script >> *somewhere* that points $PATH to the proper Postgres binaries. >> >> On 1/17/20 6:56 AM, Ramesh Penuballi wrote: >> >> Dear Ron, >> >> Thanks for looking into this query. >> >> Could you please let me know how can I check the same. >> >> We are able take our production backup from the Jenkins server, and the >> issue is with our uat database server only. >> >> Could you please let me know how to check the path on Jenkins server as I >> am new to Jenkins. >> >> Thanks and Regards >> Ramesh Penuballi >> >> On Fri, 17 Jan, 2020, 17:53 Ron, wrote: >> >>> Does the Jenkins' $PATH variable have the Postgres binaries' location? >>> >>> On 1/17/20 2:47 AM, ramesh penumalli wrote: >>> >>> Dear Team, >>> >>> I am using postgresql 9.2.12 version and I am facing an issue while >>> running the executing the commands createdb,dropdb,psql commands in the >>> shell script which is running from Jenkins to restore prod data into stage >>> environment. >>> >>> Jenkins job is failing with the below output.I have verified the script >>> and the scrit is fine and it seems to be an issue with the configuration of >>> postgresql and not sure where to make the changes. >>> >>> line 48: dropdb: command not found >>> >>> line 50: createdb: command not found >>> >>> line 52: psql: command not found >>> >>> Kindly guide me to rectify the issue. >>> >>> Thanks and Regards, >>> >>> Ramesh >>> >>> >>> -- >>> Angular momentum makes the world go 'round. >>> >> >> -- >> Angular momentum makes the world go 'round. >> >
Re: minimal wal_level on subscriber
Le 17/01/2020 à 19:07, Laurenz Albe a écrit : On Fri, 2020-01-17 at 08:43 +0100, Arnaud L. wrote: > > is it OK to set wal_level to minimal on the subscriber side of the > > logical replication ? > > Yes, if you don't want physical backups. You mean online physical backups ? I could still shutdown the subscriber and take an offline physical backup, right ? Yes, that is what I mean. Great. Thanks Laurenz ! Regards -- Arnaud
