Обсуждение: problem with pg_stat_activity view
Hello,
postgres version: 8.2.0.4
platform windows: server 2008
I check connections using:
SELECT pg_stat_get_backend_idset()
Now, I take the output and try to get the ip addresses using
select pg_stat_get_backend_client_addr(<integer>);
but for a local connection (simple login using psql), I get the next error:
ERROR: invalid input syntax for type inet: "fe80::104d:416e:a8dc:c02e%12"
SQL state: 22P02
The same error occurs for simple “select * pg_stat_activity” (of course, as it is based on the former function)
I noticed that for netstat –r –n, I got the next line (among the others):
12 266 fe80::104d:416e:a8dc:c02e/128
May the last 8 was removed and therefore the ip became illegal ?
Need your help
Thanks,
Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
yuval_sofer@bmc.com
fe80::104d:416e:a8dc:c02e %12 Since %12 is a "space" character, it seems first thing to do might be an extra space in your code. Just a quick guess. Regards, Ben Kim On Mon, 8 Sep 2008, Sofer, Yuval wrote: > Hello, > > > > postgres version: 8.2.0.4 > > platform windows: server 2008 > > > > I check connections using: > > > > SELECT pg_stat_get_backend_idset() > > > > Now, I take the output and try to get the ip addresses using > > > > select pg_stat_get_backend_client_addr(<integer>); > > > > but for a local connection (simple login using psql), I get the next > error: > > > > ERROR: invalid input syntax for type inet: > "fe80::104d:416e:a8dc:c02e%12" > > SQL state: 22P02 > > > > The same error occurs for simple "select * pg_stat_activity" (of course, > as it is based on the former function) > > > > I noticed that for netstat -r -n, I got the next line (among the > others): > > > > 12 266 fe80::104d:416e:a8dc:c02e/128 > > > > May the last 8 was removed and therefore the ip became illegal ? > > > > Need your help > > > > Thanks, > > > > Yuval Sofer > BMC Software > CTM&D Business Unit > > DBA Team > 972-52-4286-282 > yuval_sofer@bmc.com > > > >
Sorry, I apologize for the mistake. %20 is the space, and the extra %12 seems to be DC3 (device control 3). Regards, Ben Kim On Mon, 8 Sep 2008, Ben Kim wrote: > > fe80::104d:416e:a8dc:c02e %12 > > Since %12 is a "space" character, it seems first thing to do might be an extra > space in your code. Just a quick guess. > > Regards, > > Ben Kim > > > On Mon, 8 Sep 2008, Sofer, Yuval wrote: > >> Hello, >> >> >> >> postgres version: 8.2.0.4 >> >> platform windows: server 2008 >> >> >> >> I check connections using: >> >> >> >> SELECT pg_stat_get_backend_idset() >> >> >> >> Now, I take the output and try to get the ip addresses using >> >> >> >> select pg_stat_get_backend_client_addr(<integer>); >> >> >> >> but for a local connection (simple login using psql), I get the next >> error: >> >> >> >> ERROR: invalid input syntax for type inet: >> "fe80::104d:416e:a8dc:c02e%12" >> >> SQL state: 22P02 >> >> >> >> The same error occurs for simple "select * pg_stat_activity" (of course, >> as it is based on the former function) >> >> >> >> I noticed that for netstat -r -n, I got the next line (among the >> others): >> >> >> >> 12 266 fe80::104d:416e:a8dc:c02e/128 >> >> >> >> May the last 8 was removed and therefore the ip became illegal ? >> >> >> >> Need your help >> >> >> >> Thanks, >> >> >> >> Yuval Sofer >> BMC Software >> CTM&D Business Unit >> >> DBA Team >> 972-52-4286-282 >> yuval_sofer@bmc.com >> >> >> >> > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
>> ERROR: invalid input syntax for type inet:
>> "fe80::104d:416e:a8dc:c02e%12"
>>
>> SQL state: 22P02
Interesting.
When we HAVE_IPV6, there's code to remove stuff after a % in an address.
My bet is that what you're running was not compiled with HAVE_IPV6 and
so the code is not executed. Obviously this is invoked in
pg_stat_get_backend_client_addr().
/*
* clean_ipv6_addr --- remove any '%zone' part from an IPv6 address string
*
* XXX This should go away someday!
*
* This is a kluge needed because we don't yet support zones in stored inet
* values. Since the result of getnameinfo() might include a zone spec,
* call this to remove it anywhere we want to feed getnameinfo's output to
* network_in. Beats failing entirely.
*
* An alternative approach would be to let network_in ignore %-parts for
* itself, but that would mean we'd silently drop zone specs in user input,
* which seems not such a good idea.
*/
void
clean_ipv6_addr(int addr_family, char *addr)
{
#ifdef HAVE_IPV6
if (addr_family == AF_INET6)
{
char *pct = strchr(addr, '%');
if (pct)
*pct = '\0';
}
#endif
}
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes: > ERROR: invalid input syntax for type inet: > "fe80::104d:416e:a8dc:c02e%12" > > SQL state: 22P02 > Interesting. > When we HAVE_IPV6, there's code to remove stuff after a % in an address. We do now ... it wasn't there in 8.2.0. http://archives.postgresql.org/pgsql-committers/2007-05/msg00268.php regards, tom lane
That explains it... I thought it was a precent encoding but at the same
time a strange place for a control character.
Regards,
Ben Kim
On Mon, 8 Sep 2008, Alvaro Herrera wrote:
>
>>> ERROR: invalid input syntax for type inet:
>>> "fe80::104d:416e:a8dc:c02e%12"
>>>
>>> SQL state: 22P02
>
> Interesting.
>
> When we HAVE_IPV6, there's code to remove stuff after a % in an address.
> My bet is that what you're running was not compiled with HAVE_IPV6 and
> so the code is not executed. Obviously this is invoked in
> pg_stat_get_backend_client_addr().
>
> /*
> * clean_ipv6_addr --- remove any '%zone' part from an IPv6 address string
> *
> * XXX This should go away someday!
> *
> * This is a kluge needed because we don't yet support zones in stored inet
> * values. Since the result of getnameinfo() might include a zone spec,
> * call this to remove it anywhere we want to feed getnameinfo's output to
> * network_in. Beats failing entirely.
> *
> * An alternative approach would be to let network_in ignore %-parts for
> * itself, but that would mean we'd silently drop zone specs in user input,
> * which seems not such a good idea.
> */
> void
> clean_ipv6_addr(int addr_family, char *addr)
> {
> #ifdef HAVE_IPV6
> if (addr_family == AF_INET6)
> {
> char *pct = strchr(addr, '%');
>
> if (pct)
> *pct = '\0';
> }
> #endif
> }
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
Hi Everybody,
Is it possible to move the directory associated to a tablespace and update that information in the postgres cluster?
I was expecting something like : ALTER TABLESPACE LOCATION “C:\...” but found no such usage. Looking it up in the documentation I found that on linux one could
- shutdown postgres instance
- move the directory
- fix the symbolic link in $PGDATA/pg_tblspc
- start postgres instance
- update the pg_tablespace entry
I am running postgres 8.3.0 on windows 2003 sp2, according to wikipedia windows “symbolic links” have been implemented on xp only, so my platform would not have them. Altering the pg_tablespace it’s self and moving the directory does not work, I get the following error: could not create directory “pg_tblspc/16439/111451”; No such file or directory.
Questions:
- is it possible to move a tablespace ?
- how does the instance keep track of the pg_tblspc contents if there are no symbolic links on windows?
Thank you,
Sincerely,
Kasia