Re: [GENERAL] Error message "psql: could not connect to server: No such file or directory"
> sudo su - _postgres /usr/local/pgsql-9.1/bin/initdb -U postgres -D > /usr/local/pgsql-9.1/data --encoding=UTF8 --locale=en_US > sudo su - _postgres /usr/local/pgsql-9.1/bin/pg_ctl start -D > /usr/local/pgsql-9.1/data > > Although I don't get an error message, I don't have the feeling that it > started the server. There is still nothing in the process list, and a normal > "psql" results in the same message as before "psql: could not connect…" > > So sorry for bothering you guys… just being really frustrated now… > > If you have any ideas, please let me know… :-) I just went back to the postgres documentation [1] (actually reading all google results on the error message one after the other) and tried out what was said there (adapted to my conditions): sudo -u _postgres /usr/local/pgsql-9.1/bin/postgres -D /usr/local/pgsql-9.1/data and suddenly postgres runs…. [1] http://www.postgresql.org/docs/9.1/static/server-start.html -- 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] Problem installing extensions on Lion
>>> 1) Do the above files actually exist in the above location? > >> Yes, the c.h exist in the mentioned location. However, as it does not >> indicate where it looks for the stdio.h, perhaps the problem lies there. > > Indeed. Where did you get the advice to use "-isysroot > /Developer/SDKs/MacOSX10.6.sdk"? According to the gcc manual, > that means > > Use DIR as the logical root directory for headers and libraries. > For example, if the compiler would normally search for headers in > `/usr/include' and libraries in `/usr/lib', it will instead search > `DIR/usr/include' and `DIR/usr/lib'. > > So basically, that breaks every attempt to use a standard header from > /usr/include, unless the SDK has provided a substitute, which evidently > it mostly doesn't. > > [ pokes around on own Mac... ] Hmm, for me the file > /Developer/SDKs/MacOSX10.6.sdk/usr/include/stdio.h > does exist with reasonably sane-looking contents. So maybe your problem > is a broken SDK installation. Just as an information: It's really the Xcode which is placed now in /Applications, instead of /Developer. I copied over my older version of Xcode to the /Developer directory, and the compilation worked fine... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump not dumping all tables
Hi there, I am pg_dump-ing all tables from schema public on the server /usr/local/pgsql/bin/pg_dump -U user my_database --schema=public --encoding=UTF-8 > dump.sql and re-loading it via psql on my local machine. But instead of having 708 tables as on the server, I end up with only 570 on my local machine. When trying to search for the name of a missing table in the dump file, it is indeed not there. Owner of the table is the same as on all other tables… What could that be? Thanks for any hint! Stef -- 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] pg_dump not dumping all tables
On 22 June 2012 10:45, Stefan Schwarzer wrote: > Hi there, > > I am pg_dump-ing all tables from schema public on the server > > /usr/local/pgsql/bin/pg_dump -U user my_database --schema=public > --encoding=UTF-8 > dump.sql > > and re-loading it via psql on my local machine. > > But instead of having 708 tables as on the server, I end up with only 570 on > my local machine. > > When trying to search for the name of a missing table in the dump file, it is > indeed not there. Owner of the table is the same as on all other tables… > > What could that be? Are you sure the missing tables are in the public schema? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Starting a cluster as a service
Hi again, I'm running "PostgreSQL 9.1" under "Windows XP". I'm still trying to set a proper logging system for a PostgreSQL cluster "a_pg_cluster". In the cluster "postgresql.conf" configuration file, I uncommented "logging_connector = on". When I stopped + started the service, I could see a "pg_log" directory had been created with a log file inside. Now, I would like: 1) to store the cluster log files somewhere else say, for example: "C:\postgresql\logs\a_pg_cluster\" 2) to have several small log files instead of a big one and log rotation. Yet, when I set the "log_directory" parameter in the following way: "log_directory = 'C:\postgresql\logs\a_pg_cluster\'" and stop + start the service, it fails with always the same unusable message: "The a_pgcluster_srv service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service." In the manual, one can read: "When logging_collector is enabled, this parameter determines the directory in which log files will be created. It can be specified as an absolute path, or relative to the cluster data directory. This parameter can only be set in the postgresql.conf file or on the server command line." So, what's wrong with it? I would be happy with the following configuration: logging_collector = on log_directory = "C:\postgresql\logs\a_pg_cluster\" log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_size = 10MB log_min_messages = PANIC log_error_verbosity = VERBOSE except, it doesn't work... and I'm not sure about the levels "PANIC" and "VERBOSE". Can you help? Thank you and best regards. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728p5713888.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Extensions and roles for access administration
Greetings. I've developed a small extension, that is essentially a collection of tables with a bunch of PL/pgSQL functions, that are API for the whole thing. Inside the extension script I am creating extra roles, and access to the extension's functions is provided using these extra roles. Given extension is named 'dmf' and one of the roles is 'dmf_user', administrator has to grant 'dmf_user' to any user that is eligible to use the provided APIs. My questions are: - is this the correct approach to include into extension a set of administrative roles? - I've noticed, that after I DROP EXTENSION, all roles are still there in the database. Is this expected? -- Victor Y. Yegorov
Re: [GENERAL] Starting a cluster as a service
On Fri, Jun 22, 2012 at 5:36 PM, Léa Massiot wrote: > Hi again, > > I'm running "PostgreSQL 9.1" under "Windows XP". > I'm still trying to set a proper logging system for a PostgreSQL cluster > "a_pg_cluster". > > In the cluster "postgresql.conf" configuration file, I uncommented > "logging_connector = on". > When I stopped + started the service, I could see a "pg_log" directory had > been created with a log file inside. > > Now, I would like: > 1) to store the cluster log files somewhere else say, for example: > "C:\postgresql\logs\a_pg_cluster\" > 2) to have several small log files instead of a big one and log rotation. > > Yet, when I set the "log_directory" parameter in the following way: > "log_directory = 'C:\postgresql\logs\a_pg_cluster\'" and stop + start the service, it fails with always the same unusable > message: > "The a_pgcluster_srv service on Local Computer started and then stopped. > Some services stop automatically if they have no work to do, for example, > the Performance Logs and Alerts service." > > In the manual, one can read: > "When logging_collector is enabled, this parameter determines the directory > in which log files will be created. It can be specified as an absolute > path, > or relative to the cluster data directory. This parameter can only be set > in > the postgresql.conf file or on the server command line." > > So, what's wrong with it? > > I would be happy with the following configuration: > > logging_collector = on > log_directory = "C:\postgresql\logs\a_pg_cluster\" > log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' > log_rotation_size = 10MB > log_min_messages = PANIC > log_error_verbosity = VERBOSE > > except, it doesn't work... and I'm not sure about the levels "PANIC" and > "VERBOSE". > > Can you help? > > Here two thing's, One you should have full access on the directory for Postgres User. Second, you need to give directory name as below. log_directory = 'C:\\postgresql\\logs\\a_pg_cluster' --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Extensions and roles for access administration
=?UTF-8?B?0JLQuNC60YLQvtGAINCV0LPQvtGA0L7Qsg==?= writes: > - I've noticed, that after I DROP EXTENSION, all roles are still there in > the database. Is this expected? Roles are not considered to be part of an extension: they really can't be, since an extension is local to a database while a role is global to the whole installation. As per the documentation, you're allowed to do CREATE ROLE in an extension script but it won't be managed by the extension mechanism. Not sure about a better solution to your problem offhand. It might be sane for the script to do CREATE ROLE IF NOT EXISTS, if we had that, which we do not :-( 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
[GENERAL] insert select fails inside of function
Hi All, I am scratching my head over this one. I have a basic function which populates a table inside of it (declared outside of the function but that shouldn't matter) and it requires something like insert into table1 (col1, ...) select * from foo and I get the error "query has no destination for result data". This is surely not the case as it's going directly into the insert statement which doesn't have output. Any idea how to get around this particularly annoying problem? I have to say, making the switch from t-sql to postgresql has been fairly easy except for function debugging. The inability to return a select statement from a function and have it display has caused hours of wasted time and debugging headaches. Is this going to change any time soon? Thanks, ~Ben
Re: [GENERAL] Extensions and roles for access administration
Thanks, this clarifies things for me. There's DROP ROLE IF EXISTS, which I'm using. 2012/6/22 Tom Lane > > Roles are not considered to be part of an extension: they really can't > be, since an extension is local to a database while a role is global to > the whole installation. As per the documentation, you're allowed to do > CREATE ROLE in an extension script but it won't be managed by the > extension mechanism. > > Not sure about a better solution to your problem offhand. It might be > sane for the script to do CREATE ROLE IF NOT EXISTS, if we had that, > which we do not :-( > -- Victor Y. Yegorov
Re: [GENERAL] insert select fails inside of function
Benedict Holland writes: > I am scratching my head over this one. I have a basic function which > populates a table inside of it (declared outside of the function but that > shouldn't matter) and it requires something like > insert into table1 (col1, ...) > select * from foo > and I get the error "query has no destination for result data". This is > surely not the case as it's going directly into the insert statement which > doesn't have output. Any idea how to get around this particularly annoying > problem? What PG version? Could we see the exact text of the function? I'm wondering about aliasing problems stemming from function variables named similarly to the target table or its columns --- newer PG versions are brighter about that sort of conflict than older ones. > I have to say, making the switch from t-sql to postgresql has been > fairly easy except for function debugging. RAISE NOTICE is the usual substitute for what I think you were doing on t-sql. Also, I think EDB is still supporting their plpgsql debugger, so you might consider experimenting with that. 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] insert select fails inside of function
Hi Tom, Thanks for the response. The PG version is 9.0. I can't really give you the text of the function unfortunately. I know though that there isn't any aliasing issues occurring. After commenting out all but one line, I have it down to, not a insert select but a create temp table t1(id, ...) as select (a few columns with names that don't match) where etc. As by the exact syntax specified http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html: *Tip:* Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT. This is the only thing still left uncommented in the function. Thanks, ~Ben On Fri, Jun 22, 2012 at 3:44 PM, Tom Lane wrote: > Benedict Holland writes: > > I am scratching my head over this one. I have a basic function which > > populates a table inside of it (declared outside of the function but that > > shouldn't matter) and it requires something like > > > insert into table1 (col1, ...) > > select * from foo > > > and I get the error "query has no destination for result data". This is > > surely not the case as it's going directly into the insert statement > which > > doesn't have output. Any idea how to get around this particularly > annoying > > problem? > > What PG version? Could we see the exact text of the function? I'm > wondering about aliasing problems stemming from function variables named > similarly to the target table or its columns --- newer PG versions are > brighter about that sort of conflict than older ones. > > > I have to say, making the switch from t-sql to postgresql has been > > fairly easy except for function debugging. > > RAISE NOTICE is the usual substitute for what I think you were doing on > t-sql. Also, I think EDB is still supporting their plpgsql debugger, so > you might consider experimenting with that. > >regards, tom lane >
Re: [GENERAL] insert select fails inside of function
Benedict Holland writes: > Thanks for the response. The PG version is 9.0. I can't really give you the > text of the function unfortunately. I know though that there isn't any > aliasing issues occurring. After commenting out all but one line, I have it > down to, not a insert select but a > create temp table t1(id, ...) as > select (a few columns with names that don't match) > where etc. Hm. A CREATE TABLE AS SELECT case works for me in 9.0.8. Perhaps you could sanitize what you've got into a small self-contained test case? 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] Feature discussion: Should syntax errors abort a transaction?
On Wed, 2012-06-20 at 00:24 -0700, Chris Travers wrote: > I guess it seems to me that I would not object to a new option for > transaction behavior where one could do something like SET TRANSACTION > INTERACTIVE; and have no errors abort the transaction at all (explicit > commit or rollback required) but I would complain loudly if this were > to be the default, and I don't see a real need for it. It's already available in psql. See ON_ERROR_ROLLBACK: http://www.postgresql.org/docs/9.2/static/app-psql.html Regards, Jeff Davis -- 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] retrieving function raise messages in ecpg embedded sql code
On Mon, 2012-06-18 at 21:35 +, Haszlakiewicz, Eric wrote:
> I'm trying to get some additional information back from a trigger to my
> embedded SQL
> program, to essentially emulate Informix's way of generating serial values.
> I can get the serial to be generated, but I'm trying to figure out how to get
> the
> generated value back to my program with minimal changes to the SQL.
Have you already looked at INSERT...RETURNING?
http://www.postgresql.org/docs/9.2/static/sql-insert.html
> I can't figure out how to retrieve the message raised by the trigger. I know
> it's
> available in some cases, because I see the message when I insert a row
> through psql,
> but even things like this:
>printf("%s\n", PQerrorMessage(ECPGget_PGconn(mydb)));
>
> return nothing useful. Is there a way to get this information?
Yes, these messages are delivered via notice processing (not to be
confused with LISTEN/NOTIFY):
http://www.postgresql.org/docs/9.2/static/libpq-notice-processing.html
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] configuring queries for concurrent updates
[std_disclaimer]I'm not a DBA and I'm running PostgreSQL on a quad-core Intel laptop. You may read on after you stop laughing...[/std_disclaimer] I've written a version of UPSERT designed to import a large number of records. It works in two steps: it UPDATEs incumbent records that share common keys with the new records, then INSERTs new records that do not share common keys with the incumbent records. The gist of it is: SAVEPOINT ...; -- update records from subquery that share common keys with tbl UPDATE tbl SET f1 = X.f1, f2 = X.f2, ... FROM (subquery) AS X WHERE tbl.k1 = X.k1 AND tbl.k2 = X.k2 AND ...; -- insert records from subquery that do NOT share common keys with tbl INSERT INTO tbl (f1, f2, ...) SELECT Y.f1, Y.f2, ... FROM (subquery) AS Y LEFT OUTER JOIN tbl ON tbl.k1 = Y.k1 AND tbl.k2 = Y.k2 AND ... WHERE tbl.id IS NULL; RELEASE SAVEPOINT ...; The (subquery) frequently generates 5000 records for update/insert. Three Unix processes are running the import process concurrently, all updating/inserting records into a common table. Most of the time this works, but I've observed examples where an import process will * get a "PG::Error: ERROR: current transaction is aborted, commands ignored until end of transaction block" * get a "PG::Error: ERROR: deadlock detected" * go catatonic for several minutes This leads me to believe that my SAVEPOINT / RELEASE SAVEPOINT is not the correct way to protect the system from multiprocessing mischief. I've read Tom Lane's presentation on concurrency (http://www.postgresql.org/files/developer/concurrency.pdf), but I haven't been able to figure out the right solution. [For what it's worth, with the data I'm importing, INSERTs will far outnumber the UPDATES.] Since I someday plan to spin this code up on a big system with >> 3 import processes, I'd like to get this right. Hints and solutions warmly accepted. Thanks. - rdp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
