Re: [BUGS] BUG #6689: socket file name convention doesn't allow different IPs and the same port for different pgclusters
On Fri, Jun 15, 2012 at 7:33 PM, Mark Schneider wrote: > Am 15.06.2012 07:56, schrieb Magnus Hagander: > >> On Wed, Jun 13, 2012 at 2:45 AM, wrote: >>> >>> The following bug has been logged on the website: >>> >>> Bug reference: 6689 >>> Logged by: Mark >>> Email address: m...@it-infrastrukturen.org >>> PostgreSQL version: 9.1.3 >>> Operating system: unbuntu-server 12.04 LTS >>> Description: >>> >>> When using /var/run/postgresql/ as unix_socket_directory for "main" and >>> "second" pgcluster and different IP-interfaces (addresse) for every >>> pgcluster (but the same default port number), there are different pid >>> file >>> names (like 9.1-main.pid and 9.1-secondpg.pid) *but* names of unix_sockek >>> files doesn't follow such name conventions. >>> >>> It results in error when trying to run the second pgcluster with another >>> IP >>> but the same (default port 5432): >>> Error: Port conflict: another instance is already running on on >>> /var/run/postgresql with port 5432 >> >> First of all, this is not a bug - this is intended behaviour. Perhaps >> the documentation needs to be clearified on the fact that the port >> number needs to be unique across all instances though. > > Even it is not an implementation bug it is a kind of *conception* bug from > my point of view. That's why I'm suggesting updating the documentation to make that more clear. > There is *no* real reason why port numbers should be different for different > IP addresses. True. But as I said, it doesn't just control the TCP port *or* the unix socket name. > There is anything about running further psql instances on different ethernet > interfaces (on one box) in the current doc yet. Right. That's my suggested fix. >> One reason is that the port number is used to control several other >> things. Just the Unix socket is only one of the things - it also >> controls the name of shared memory segments, so it still won't work if >> you work around that. > > When I want to run two separate psql instances they should use also separate > shared memory segments. Exaclty. Which is why they need separate port numbers. If they use the same port number, they'd use the same shared memory segment... >> All instances need to have their own ports. If you want to listen on >> the same port on different IPs, you will need to use something like >> kernel level ip forwarding to rewrite the access, and actually run >> PostgreSQL on different ports still. > > Using different port number for the second ethernet interface works as > expected. Yes. For just the ethernet interface. But again, the parameter controls more things. > How complex is it to implement different unix_socket names in the sourse > code? > (when further instances listen on different ethernet interfaces) Probably not hugely so, but it wouldn't fix your problem. There's an ongoing discussoin on-hackers about being able to listen to multiple unix sockets, and multiple port numbers. But as you'll see if you read up on that one (see archives.postgresql.org), it's all still requiring a single "primary port" to control the other parts. So that also won't solve your problem. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] pg_upgrade fails with missing FTS resources
Hello, pg_upgrade fails on missing FTS dictionaries requiring external files. --check fails to detect the incompatibility. E.g. after installed italian_fts (http://pgxn.org/dist/italian_fts/) into a database, the pg_upgrade_dump_db.sql contains the command: CREATE TEXT SEARCH DICTIONARY italian_ispell ( TEMPLATE = pg_catalog.ispell, dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords = 'italian_ispell' ); that fails with a: psql:/var/lib/postgresql/pg_upgrade_dump_db.sql:56891: ERROR: could not open dictionary file "/usr/share/postgresql/9.1/tsearch_data/italian_ispell.dict": No such file or directory I think it would be enough to detect the incompatibility at --check time, as much as it's done for the required libraries, and only for the builtin dict types. If you want the issue to be fixed I can try to fix it myself: it seems easy enough. -- Daniele -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6689: socket file name convention doesn't allow different IPs and the same port for different pgclusters
Hello Magnus, Thanks a lot for your time checking my email. Am 15.06.2012 07:56, schrieb Magnus Hagander: On Wed, Jun 13, 2012 at 2:45 AM, wrote: The following bug has been logged on the website: Bug reference: 6689 Logged by: Mark Email address: m...@it-infrastrukturen.org PostgreSQL version: 9.1.3 Operating system: unbuntu-server 12.04 LTS Description: When using /var/run/postgresql/ as unix_socket_directory for "main" and "second" pgcluster and different IP-interfaces (addresse) for every pgcluster (but the same default port number), there are different pid file names (like 9.1-main.pid and 9.1-secondpg.pid) *but* names of unix_sockek files doesn't follow such name conventions. It results in error when trying to run the second pgcluster with another IP but the same (default port 5432): Error: Port conflict: another instance is already running on on /var/run/postgresql with port 5432 First of all, this is not a bug - this is intended behaviour. Perhaps the documentation needs to be clearified on the fact that the port number needs to be unique across all instances though. Even it is not an implementation bug it is a kind of *conception* bug from my point of view. There is *no* real reason why port numbers should be different for different IP addresses. There is anything about running further psql instances on different ethernet interfaces (on one box) in the current doc yet. One reason is that the port number is used to control several other things. Just the Unix socket is only one of the things - it also controls the name of shared memory segments, so it still won't work if you work around that. When I want to run two separate psql instances they should use also separate shared memory segments. All instances need to have their own ports. If you want to listen on the same port on different IPs, you will need to use something like kernel level ip forwarding to rewrite the access, and actually run PostgreSQL on different ports still. Using different port number for the second ethernet interface works as expected. How complex is it to implement different unix_socket names in the sourse code? (when further instances listen on different ethernet interfaces) regards, Mark -- m...@it-infrastrukturen.org http://rsync.it-infrastrukturen.org -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6694: 9.2 beta 2 : psql commands \db and \db+ fail
Hello, paf=# \db ERROR: column "spclocation" does not exist LINE 3: spclocation AS "Location" ^ Are you using the psql provided by 9.2 beta 2? Or an older version? It appears that the version of psql in 9.2 beta 2 does the correct thing, look at src/bin/psql/describe.c line 142 or so. Yeah, the OP must be using a 9.1 or earlier psql to connect to the 9.2beta2 server showed in his version() output. There's a reason we print out a warning like this: WARNING: psql version X.a, server version Y.b Some psql features might not work. when the major version of psql and the server it is connecting to don't match. Josh Oups ! You are right, I was not using the right psql client version!!! I am using different pg version for various tests. And I failed here to use the right PATH. Please accept my apologies for the noise. I will be more careful before reporting the next bug ;-) Best regards. Philippe. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6690: ALL and ANY array operators
sergey-1...@yandex.ru writes: > As I see in documentation, > http://www.postgresql.org/docs/current/static/functions-aggregate.html there > is an ambiguity with bool_or aggregate and ANY array operator, so bool_or > cannot has standard name ANY. So ANY should always mean array operator. But > such query produces syntax error: > select 1 = ANY((select ARRAY[1, 2]::integer[])) Yes. Do it like this instead: select 1 = ANY(ARRAY[1, 2]::integer[]); If the argument of ANY is a sub-select, the interpretation is to compare the left-hand value to each row of the sub-select's output. This isn't terribly consistent perhaps, but it's what's required by the SQL standard. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Can't move extension out of pg_catalog
After moving an extension into the pg_catalog schema, it seems impossible to move it somewhere else: test=# create extension ltree; CREATE EXTENSION test=# alter extension ltree set schema pg_catalog; ALTER EXTENSION test=# alter extension ltree set schema public; ERROR: cannot remove dependency on schema pg_catalog because it is a system object. It is still possible to drop it. Tested with PostgreSQL 9.1.3. -- Daniele -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Can't move extension out of pg_catalog
Daniele Varrazzo writes: > After moving an extension into the pg_catalog schema, it seems > impossible to move it somewhere else: > test=# create extension ltree; > CREATE EXTENSION > test=# alter extension ltree set schema pg_catalog; > ALTER EXTENSION > test=# alter extension ltree set schema public; > ERROR: cannot remove dependency on schema pg_catalog because it is a > system object. Yeah, this is an implementation restriction that is unlikely to get changed anytime soon. The problem is that moving the extension's objects into pg_catalog results in dropping all their namespace dependencies (since pg_catalog is a pinned object) and then there is no way to resurrect that data if we want to move them someplace else. See changeDependencyFor() in catalog/pg_depend.c. Possibly it would be better to disallow this ALTER in both directions, ie remove the support for the "drop the dependency" case in changeDependencyFor(). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6695: PgAdminIII
On 14-06-2012 21:17, fabio.lun...@gmail.com wrote: > Quando criado usuário(CREATE USER) com a opção CONNECTION LIMIT, a interface > gráfica do PgAdmin III parece não respeitar o parâmetro, ou está criando > novas conexões desnecessárias. Isso pode ser simulado criando um usuário com > CONNECTION LIMIT 1, dar GRANT para uma tabela e tentar acessar os dados pela > interface do PGAdmin III. Vai ocorrer mensagem de erro que o número de > conexões ultrapassou, porém isso não é verdade. > Isto *não* é um bug. Além disso, esta é uma lista em inglês. O mais apropriado é expor a sua dúvida na lista de discussão pgbr-geral [1]. Quanto ao seu problema, acho que você está utilizando um super-usuário e, neste caso, o limite não é respeitado. Vide o manual [2] para informações adicionais. [1] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral [2] http://www.postgresql.org/docs/9.1/static/sql-createrole.html -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade fails with missing FTS resources
On Fri, Jun 15, 2012 at 04:15:57PM +0100, Daniele Varrazzo wrote: > Hello, > > pg_upgrade fails on missing FTS dictionaries requiring external files. > --check fails to detect the incompatibility. > > E.g. after installed italian_fts (http://pgxn.org/dist/italian_fts/) > into a database, the pg_upgrade_dump_db.sql contains the command: > > CREATE TEXT SEARCH DICTIONARY italian_ispell ( > TEMPLATE = pg_catalog.ispell, > dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords > = 'italian_ispell' ); > > that fails with a: > > psql:/var/lib/postgresql/pg_upgrade_dump_db.sql:56891: ERROR: could > not open dictionary file > "/usr/share/postgresql/9.1/tsearch_data/italian_ispell.dict": No such > file or directory > > I think it would be enough to detect the incompatibility at --check > time, as much as it's done for the required libraries, and only for > the builtin dict types. > > If you want the issue to be fixed I can try to fix it myself: it seems > easy enough. Sure, we can test for that. What system column stores these file names? Do we have any other file references in our system tables? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade fails with missing FTS resources
Bruce Momjian writes: > On Fri, Jun 15, 2012 at 04:15:57PM +0100, Daniele Varrazzo wrote: >> pg_upgrade fails on missing FTS dictionaries requiring external files. >> --check fails to detect the incompatibility. > Sure, we can test for that. What system column stores these file names? Trying to check that on the basis of the system catalog entries seems quite hopeless. What's in the catalogs is essentially what appears in the CREATE command, ie dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords = 'italian_ispell' The dictionary template knows which of these options are file names and which are not, and what filename extension to apply to each option that is a file name; but you don't. The closest that I think pg_upgrade could reasonably come is to compare the contents of the old and new installations' tsearch_data/ directories, and complain about any files present in the former and not the latter. However, that method seems fraught with opportunities for false positives: in particular you might complain about some file that was in the old installation but not actually referenced by any text search catalog entry. In the end I'm not sure it's worth it. There are any number of ways that the restore step can fail, and it's impossible for pg_upgrade to pre-check for all of them. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade fails with missing FTS resources
On Fri, Jun 15, 2012 at 10:27:59PM -0400, Tom Lane wrote: > Bruce Momjian writes: > > On Fri, Jun 15, 2012 at 04:15:57PM +0100, Daniele Varrazzo wrote: > >> pg_upgrade fails on missing FTS dictionaries requiring external files. > >> --check fails to detect the incompatibility. > > > Sure, we can test for that. What system column stores these file names? > > Trying to check that on the basis of the system catalog entries seems > quite hopeless. What's in the catalogs is essentially what appears > in the CREATE command, ie > > dictfile = 'italian_ispell', afffile = 'italian_ispell', stopwords = > 'italian_ispell' > > The dictionary template knows which of these options are file names and > which are not, and what filename extension to apply to each option that > is a file name; but you don't. > > The closest that I think pg_upgrade could reasonably come is to compare > the contents of the old and new installations' tsearch_data/ > directories, and complain about any files present in the former and not > the latter. However, that method seems fraught with opportunities for > false positives: in particular you might complain about some file that > was in the old installation but not actually referenced by any text > search catalog entry. > > In the end I'm not sure it's worth it. There are any number of ways > that the restore step can fail, and it's impossible for pg_upgrade > to pre-check for all of them. Agreed. Not worth it. If it is reported again, we can document this requirement. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
