Re: [GENERAL] Looking for auto starting procedures
Hello ! With server start, I mean the start of the postgresql engine, not the machine itself. I thought about a stored procedure to come into a LISTEN in a loop, but the loop must be started anyway [avoid a separate daemon {now, I know, I can use a pgsql script}]. LISTEN looks great to me [not tried yet]. Thanks for the pointers, I have yet not heard about PGQ and it looks like something I missed on Sql Server! Will study this! For logging:I think, I have all rights, but not postgresql configured to log to syslog. Much thanks! br++mabra -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Cédric Villemain Sent: Saturday, December 04, 2010 3:12 PM To: ma...@manfbraun.de Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Looking for auto starting procedures 2010/12/2 ma...@manfbraun.de: Hello ! And thanks to all, for answers. Naturally, cron does not operate on DB tables and if I add/remove/change a line in a control table dynamically, cron would not notice that ;-) So I had to write a daemon, which acts on that table. This might happen about ~5 - 20 seconds and cron is operates on minutes only. Perhaps you are trying to solve something with SQL server behavior and you may find more interesting ways à la PostgreSQL. I wonder if you are not looking after something like PgQ[1] or LISTEN/NOTIFY [2] An auto running stored procedure would solve the problem, if therewould be a way to run this procedure on server startup automatically [as it looks, write another daemon for this]. on server start ?! no daemon is necesary. The usual notification from postgreSQL does not allow to write an [own, better to evaluate] identifier in the syslog. This is not a matter of the syslog daemon, its on the program which logs. May be, I have just not found this. Maybe you need to adjust your 'roles' [3] so that log lines can be identified to your role. Maybe you'll have better answers if you define your objectives. [1] http://wiki.postgresql.org/wiki/PGQ_Tutorial [2] http://www.postgresql.org/docs/current/interactive/sql-notify.html [3] http://www.postgresql.org/docs/current/interactive/sql-createrole.html -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgadmin3 and pgdesigner source
I installed pgsql 9.0 from source on my linux box, where can I get the latest pgadmin3 and pgdesigner source? -- Kind Regards, Rajinder Yadav | DevMentor.org | Do Good! ~ Share Freely GNU/Linux: 2.6.35-22-generic Kubuntu x86_64 10.10 | KDE 4.5.1 Ruby 1.9.2p0 | Rails 3.0.1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] One-click Installers for 9.1 Alpha 2 (ETA)
I've recently installed newest Postgres 9.1 Alpha 1 (postgresql-9.1alpha1-windows-binaries.zip from http://www.enterprisedb.com/products/pgbindownload.do ) and got a similar question around this: There's pgAdmin3 v.1.13 included but it still complains when opening a 9.1alpha db saying: Warning: This version of pgAdmin has only been tested with PostgreSQL version 9.0 (...). Please upgrade pgAdmin. But when I look at http://www.pgadmin.org/download/windows.php it only shows pgAdmin v1.12.1 (even source code is'nt newer). So Please upgrade... is just a standard text? Yours, S. 2010/11/9 Dave Page dp...@pgadmin.org: On Mon, Nov 8, 2010 at 3:06 PM, Richard Broersma richard.broer...@gmail.com wrote: I'm interested in playing with some of the features in the Alpha 2. Is there an ETA for the release for the one-click installer? I'm not sure if I'll get time to build them this time. Unfortunately the release of the tarballs coincided with PGWest, so I couldn't do them then and now have to catch up on a bunch of work. To make matters worse, the pgAdmin build has changed somewhat on Windows and requires some effort to update the installers to work again. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table design - postgresql solution
Hi, I have a bit of a DB design question, associated with postgresql in particular, hopefully thinking it could solve my dilemma. This is my setup of 3 tables: Table_1 id_t1 name date_of_discovery history Table_2 id_t2 name type size Table_3 id_t3 name location color I want a solution (table or groups of tables) where i can establish relations between items of every table, for example: row with id=2 from Table_1 is connected to row with id=23 from Table_3 id=9(from Table_2) is connected to id=83(from Table_1) The common in many to many relations is to create for each two tables joining table tab1_tab2 with corresponding foreign keys to tab_1 and tab_2. If you want make general relation table, to keep all relations try with CHECK CONSTRAINT with own function inside. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] an enhancement idea
one of the reasons PostgreSQL is less popular with shared hosting services is that there is insufficient isolation between database users. For instance, one user leaves a Idle in TransactioN pending due to sloppy programming, and the entire cluster eventually can't be vacuumed. There's numerous other places where the isolation between postgres users is insufficient (visibility of information in pg_catalog, for instance). Also, that other 'big name' commercial database has a concept of a listener, where multiple database 'clusters' can be accessed via the same port. I have an idea that combines both of these. We add a new type of super tablespace to postgres, we'll call these 'instances' (thats what the Big O calls them, anyways). An instance has its own WAL logging, WAL writer, and its own autovacuum, and is associated with one or more databases. A given user can have a default 'instance' such that any database they create will be in that instance. An instance can optionally contain multiple tablespaces. An instance can contain multiple databases, these databases still have schemas in them. All users are common to and managed in the root instance. The instances could be associated with either the user, or with the databases, I'm not sure which is more appropriate. An issue to be resolved: Should each of these 'instances' have its own pg_catalog, or should all instances continue to use the master pg_catalog ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgadmin3 and pgdesigner source
On 05/12/2010 11:06, Rajinder Yadav wrote: I installed pgsql 9.0 from source on my linux box, where can I get the latest pgadmin3 and pgdesigner source? Dunno about pgDesigner, but pgAdmin is here: http://www.pgadmin.org/download/source.php Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] an enhancement idea
John R Pierce pie...@hogranch.com writes: one of the reasons PostgreSQL is less popular with shared hosting services is that there is insufficient isolation between database users. For instance, one user leaves a Idle in TransactioN pending due to sloppy programming, and the entire cluster eventually can't be vacuumed. There's numerous other places where the isolation between postgres users is insufficient (visibility of information in pg_catalog, for instance). If you want that level of isolation, you have to give each user his own cluster. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] an enhancement idea
On 12/05/10 12:27 PM, Tom Lane wrote: John R Piercepie...@hogranch.com writes: one of the reasons PostgreSQL is less popular with shared hosting services is that there is insufficient isolation between database users. For instance, one user leaves aIdle in TransactioN pending due to sloppy programming, and the entire cluster eventually can't be vacuumed. There's numerous other places where the isolation between postgres users is insufficient (visibility of information in pg_catalog, for instance). If you want that level of isolation, you have to give each user his own cluster. as postgresql is currently structured, yes, and further, each cluster needs its own listener port which is, IMHO, rather ugly. My idea of adding an 'instance' layer allows clusters to share ports. in fact, the first generation of this idea was to fire up a cluster for each user, but put them all under the common postmaster, which would look up the database being connected to, and point the connection's forked postgres server process at the proper cluster, much the way the Oracle listener forks Oracle instances. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] an enhancement idea
John R Pierce pie...@hogranch.com writes: On 12/05/10 12:27 PM, Tom Lane wrote: If you want that level of isolation, you have to give each user his own cluster. as postgresql is currently structured, yes, and further, each cluster needs its own listener port which is, IMHO, rather ugly. The amount of work needed to get rid of that small bit of ugliness seems far out of proportion to the value. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] an enhancement idea
If you want that level of isolation, you have to give each user his own cluster. as postgresql is currently structured, yes, and further, each cluster needs its own listener port which is, IMHO, rather ugly. The amount of work needed to get rid of that small bit of ugliness seems far out of proportion to the value. With the current architecture, you would need to develop service management that could potentially handle 100s of clusters, in a shared web host sort of environment. I sure wouldn't want to have to manage 100s of sysV /etc/rc3.d kinda services to launch all these postmasters, ugh. As I'm envisioning it, the user management would be in the master cluster, child cluster/instances would not have their own users. yeah, I suppose this is pointless. ah well, after a couple beers last night, it seemed like a fantastic idea :D -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] One-click Installers for 9.1 Alpha 2 (ETA)
On Sun, Dec 5, 2010 at 1:43 PM, Stefan Keller sfkel...@gmail.com wrote: I've recently installed newest Postgres 9.1 Alpha 1 (postgresql-9.1alpha1-windows-binaries.zip from http://www.enterprisedb.com/products/pgbindownload.do ) and got a similar question around this: There's pgAdmin3 v.1.13 included but it still complains when opening a 9.1alpha db saying: Warning: This version of pgAdmin has only been tested with PostgreSQL version 9.0 (...). Please upgrade pgAdmin. But when I look at http://www.pgadmin.org/download/windows.php it only shows pgAdmin v1.12.1 (even source code is'nt newer). So Please upgrade... is just a standard text? Yes, until we get to the point in development when we're happy to call the new version supported in any way. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] an enhancement idea
On 12/06/2010 04:41 AM, John R Pierce wrote: If you want that level of isolation, you have to give each user his own cluster. as postgresql is currently structured, yes, and further, each cluster needs its own listener port which is, IMHO, rather ugly. My idea of adding an 'instance' layer allows clusters to share ports. How do you plan to handle the use of system V shared memory? Each cluster needs its own reserved, pinned shm segment. You'll be wasting memory on idle clusters while starving busy clusters for memory. For shared hosting / multi-tenant DB needs, wouldn't it be better to improve Pg's core to handle the job better? Per-user storage quotas, database-scoped user IDs, age-limited transactions (though that can already be done pretty easily with a simple script), access-filtered views in pg_catalog, etc. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PG84 and SELinux
Tom Lane wrote: James B. Byrne byrn...@harte-lyne.ca writes: I wrote too soon. What I did was uncomment the ssl option. I neglected to change the setting from off to on. When I try to start the server with ssl=on it fails with this error: Auto configuration failed 29006:error:0E065068:configuration file routines:STR_COPY:variable has no value:conf_def.c:629:line 207 AFAIK there is no place in the standard Postgres sources that could emit an error message even vaguely like that. That looks to be the str_copy routine from conf_def.c in the OpenSSL code, i.e. line 624 of the version at: http://code.google.com/p/commitmonitor/source/browse/trunk/common/openssl/crypto/conf/conf_def.c So guessing something in the SSL autonegotiation is failing here in a really unexpected way. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services and Supportwww.2ndQuadrant.us
Re: [GENERAL] One-click Installers for 9.1 Alpha 2 (ETA)
Le 05/12/2010 14:43, Stefan Keller a écrit : I've recently installed newest Postgres 9.1 Alpha 1 (postgresql-9.1alpha1-windows-binaries.zip from http://www.enterprisedb.com/products/pgbindownload.do ) and got a similar question around this: There's pgAdmin3 v.1.13 included but it still complains when opening a 9.1alpha db saying: Warning: This version of pgAdmin has only been tested with PostgreSQL version 9.0 (...). Please upgrade pgAdmin. But when I look at http://www.pgadmin.org/download/windows.php it only shows pgAdmin v1.12.1 (even source code is'nt newer). So Please upgrade... is just a standard text? When you say even source code isn't newer, you mean there's no .tar.bz2 package with a newer release. Which is true. But development is still going on, and the master branch has support for many of the master PostgreSQL new features. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 9.0 RPMs for RHEL 6 and Fedora 14 released
2010/12/3 Devrim GÜNDÜZ dev...@gunduz.org: On Sun, 2010-11-21 at 12:39 +0300, Allan Kamau wrote: I am unable to obtain (using yum) a version of pgAdmin3 that can connect fruitfully to postgreSQL 9.x. My installation reports that the version I do have 1.10.5 is the latest. Should be fixed as of yesterday. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz Dear Devrim, It seems that I am unable to perform a successful upgrade, please have a look at the commands and outputs below. [r...@fc12-macbookpro ~]# rpm -ivh http://yum.pgrpms.org/reporpms/9.0/pgdg-fedora-9.0-2.noarch.rpm; Retrieving http://yum.pgrpms.org/reporpms/9.0/pgdg-fedora-9.0-2.noarch.rpm Preparing...### [100%] package pgdg-fedora-9.0-2.noarch is already installed [r...@fc12-macbookpro ~]# yum -y install pgadmin3; . . . . Setting up Install Process Package pgadmin3-1.10.5-1.fc12.x86_64 already installed and latest version Nothing to do You have new mail in /var/spool/mail/root [r...@fc12-macbookpro ~]# -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RESET ROLE and search_path, Connection pool
Hello, Obviously each user could use RESET ROLE and become the super user. Yes, this is a point not to forget, but isn't an issue in our case. best regards, Marc Mamin From: Derrick Rice [mailto:derrick.r...@gmail.com] Sent: Samstag, 4. Dezember 2010 00:21 To: Marc Mamin Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] RESET ROLE and search_path, Connection pool On Fri, Dec 3, 2010 at 5:13 AM, Marc Mamin m.ma...@intershop.de wrote: Hello, We are thinking about using a (java based) connection pool. An issue is that there are many different users to connect. My idea is to only have superuser connections in the pool and change the connection role (with SET ROLE) each time a user pick a connection there. Tangential to your question, but important: Obviously each user could use RESET ROLE and become the super user. This means that every piece of code that uses this pool needs to have security appropriate for code using the super user. i.e. Whatever, it's just using a read-only role, nothing bad can happen is no longer a valid argument (if it ever was). Do you have that much faith / trust in every user? * user in quotes because I'm guessing you are referring to different portions of your application / application suite and hopefully not individual persons. Derrick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general