Re: Asynchronous Trigger?
On Thu, 2018-03-29 at 22:29 +, Cory Tucker wrote: > Is it possible to have the execution of a trigger (or any function) > not block the completion of the statement they are associated with? > A pattern I had hoped to implement was to do a quick update of rows > that signaled they needed attention, and then an async per-row > trigger would come and do the maintenance (in this case, make an > expensive materialized view). This is a good use of a Notification. A trigger can queue a notification, which can be received by a listening process - which can then determine and do whatever is necessary. See the "LISTEN" documentation. -- Adam Tauno Williams <mailto:awill...@whitemice.org> GPG D95ED383 OpenGroupware Developer <http://www.opengroupware.us/>
Re: Asynchronous Trigger?
On Fri, 2018-04-27 at 11:25 -0600, Michael Loftis wrote: > As suggested, note in ToDo table, also maybe look at LISTEN and > NOTIFY and have a job runner process LISTENing We use a simple python process to listen with a PostgreSQL cursor for NOTIFY events, and push them into RabbitMQ (message broker). From there you can do a lot of asynchronous work; it will store messages, optionally with expiration, you have point-to-point or broadcast, etc... It is a very durable, scalable, and flexible way to built up an event driven system. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: Microsoft buys GitHub, is this a threat to open-source
On Thu, 2018-06-07 at 11:54 +0100, Paul Linehan wrote: > Only to the extent that GitHub was a threat before this. They never > embraced Open Source! > They cynically portrayed themselves as the developer's best pal when, > in fact, they are/were nothing more and nothing less than another > commerical software company! > I was planning to start using GitLab before this announcement, but > thechange will not affect my decision one bit - it had been made > anyway! Sourceforge! They're entire platform is Open Source, they support git, and they integrate with a variety of common open source packages. https://sourceforge.net/ -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: Microsoft buys GitHub, is this a threat to open-source
On Thu, 2018-06-07 at 13:46 +0200, Thiemo Kellner wrote: > Zitat von Achilleas Mantzios : > > Who hasn't missed sourceforge ? or ... freshmeat while we'are at it > > :) > I am sticking to sourceforge still. I never understood what people > made leave it. New-and-shiney! I've been involved in Open Source for ~30+ years; the lunging after the new-and-shiney, and these days: let's build the most impossibly complex tool chain and build system possible - is exhausting [not to mention, for many smaller projects, even trying to find|identity the "official" repository+branch]. The tool-set mania has certainly diminished my level participation; there are so many barriers before getting to the point. I am grateful that Sourceforge has been there, steadily making improvements. Their current platform is very nice. -- Adam Tauno Williams <mailto:awill...@whitemice.org> GPG D95ED383 openSUSE, a LINUX desktop for humans who need to get work done.
Re: Microsoft buys GitHub, is this a threat to open-source
On Thu, 2018-06-07 at 16:04 +0200, Thiemo Kellner, NHC Barhufpflege wrote: > Zitat von Vik Fearing : > > For many people, this is why sourceforge died: > > https://en.wikipedia.org/wiki/SourceForge#Project_hijackings_and_bu > > ndled_malware > Wow! I missed that completely. This is grave. That was also in 2015. The entire platform has been overhauled since then.
CROSSTAB( .. only one column has values... )
I'm using the crosstab feature and do not understand why I am only
getting values in the first column.
The query:
SELECT
date_trunc('month', t2.value_date) AS invoice_date,
t1.value_string AS invoice_type
COUNT(*)
FROM document d
LEFT OUTER JOIN obj_property t1
ON (t1.obj_id = d.document_id
AND t1.namespace_prefix = 'http://www.example.com/ctabs'
AND t1.value_key = 'invoiceType')
LEFT OUTER JOIN obj_property t2
ON (t2.obj_id = d.document_id
AND t2.namespace_prefix = 'http://www.example.com/ctabs'
AND t2.value_key = 'invoiceDate')
WHERE ((d.project_id = 1134510213) AND (d.is_folder = 0))
GROUP BY 1,2
- has results like -
invoice_date invoice_type count
-
2013-02 service 3454
2013-03 service 3512
2013-03 parts5366
2013-04 parts5657
2013-04 service 4612
2013-05 service 4946
2013-05 parts5508
...
So I put this in as a crosstab:
SELECT * FROM crosstab(
$$
SELECT
SUBSTR(t2.value_string, 1, 7) AS invoice_date,
t1.value_string AS invoice_type,
COUNT(*)
FROM document d
LEFT OUTER JOIN obj_property t1
ON (t1.obj_id = d.document_id
AND t1.namespace_prefix = 'http://www.example.com/ctabs'
AND t1.value_key = 'invoiceType')
LEFT OUTER JOIN obj_property t2
ON (t2.obj_id = d.document_id
AND t2.namespace_prefix = 'http://www.example.com/ctabs'
AND t2.value_key = 'invoiceDate')
WHERE ((d.project_id = 1134510213) AND (d.is_folder = 0))
GROUP BY 1,2
$$) AS final_result (invoice_date VARCHAR, "parts" BIGINT, "rental"
BIGINT, "sales" BIGINT, "service" BIGINT);
- and I get the results of -
invoice_date parts rental sales service
- -- -- ---
2001-09 1 (null) (null) (null)
2007-07 1 (null) (null) (null)
2013-02 5353 (null) (null) (null)
2013-02 3454 (null) (null) (null)
2013-03 3512 (null) (null) (null)
2013-03 5366 (null) (null) (null)
...
Only the first column has values, all the rest are NULL.
I assume I am missing something stupid.
--
Adam Tauno Williams <mailto:awill...@whitemice.org> GPG D95ED383
Systems Administrator, Python Developer, LPI / NCLA
Re: How clear the cache on postgresql?
On Fri, 2017-11-24 at 11:58 -0800, John R Pierce wrote: > On 11/24/2017 11:43 AM, Michael Nolan wrote: > > There are so many different levels of caching going on--within > > Postgresql, within the OS, within a disk array or SAN, and at the > > individual drive--that there may no longer be a meaningful way to > > perform this measurement. > generally, power cycling the server will flush all the hardware > caches AND the OS cache. Given that a real-world application will almost never experience an empty-cache scenario I question the usefulness of clearing the cache(s). I would capture transactions from a production database in order to create a load test that mimics real-world load. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: a question about oddities in the data directory
On Mon, 2017-11-27 at 15:19 +, Martin Mueller wrote: > Apologies if I asked this question before. > Is this normal behavior? Yes. > Where in the postgres documentation do I read up on this? Start here - https://www.postgresql.org/docs/10/static/admin.html > string functions and regular expressions, but it’s harder to look > under the hood. I would strongly recommend staying out from under the hood. PostgreSQL has really great tools from visualizing what is going on in the database(s); use the tools. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: Secured ldap connectivity between PostgreSQL and LDAPs server
On Fri, 2017-12-08 at 07:40 -0500, chiru r wrote: > We are using LDAP authentication for authenticating users in > PostgreSQL on Linux server and we are able to authenticate > successfully. Then one presumes you are using PAM (?) for password authentication - this question is really about pam_ldap, it is not specific to PostgreSQL in any way. > Please help me i have couple of questions to configuring LDAPS. > 1. Which location we need to keep the LDAPs Certificate files in > PostgreSQL Linux server ?. > 2. Do we need to change any configuration file for certificate > references on Linux server ? . The server should be configured to recognize certificates signed by whatever authority you are using - where they go to do that depends on your distribution. Usually that involves putting the signing certificate somewhere like /usr/share/pki/ca-trust-source/anchors/ and running "update-ca-trust". If your server already recognizes your CA you don't need to do anything other than changing PAM to use LDAPS. > 2. What need to be changed in Postgresql.conf file and pg_hba.conf > file ? Nothing, PostgreSQL just calls the PAM library. It does not care what happens beneath that. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: How to know if a database has changed
> The next day, that backup will be copied to the cloud. What does this mean? If it is rsync of a local dump to a remote use the directory dump format - disable compression - then each table which didn't change will 'copy' almost instantly. -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: PGSQL 10, many Random named DB
> 1.) PGAdmin IV bug? > 2.) Their server is hacked/cracked from outside? > 3.) A wrong configured tool, or an automation? > 4.) "Alien invasion", etc. > Did you see same thing anywhere? Are their connections to the server? What does "netstat" tell you? -- Meetings Coordinator, Michigan Association of Railroad Passengers 537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010 E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org
Re: Information on savepoint requirement within transctions
> * Why are they required in combination with failing statements (when > every other database does an "automatic savepoint and rollback" for a > failed statement)? It this statement true? I very much feel *not*. This depends on how you have set AUTO_COMMIT - and it certainly is not true for transactions of multiple statements. Informix does not do "automatic savepoint and rollback" - you will rollback the entire transaction.
