Re: [GENERAL] Error while loading sql file
On 26 Dec 2011, at 8:22, Adarsh Sharma wrote: > Dear all, > > I am facing a unique issue when I try to load an sql into a postgresql > database :- Actually, your issue isn't unique at all. You'll find it reoccurs on this list regularly, although perhaps less frequent these days. > I faced an issue some days ago & I solved the issue by the below command : > cat backup.sql | recode iso-8859-1..u8 > backup.sql That command assumes that every string in the sql file is encoded as iso-8859-1 (unless it already is unicode). > But this time the byte sequence changes to Japanese , & I fail to solve the > issue. Please let me know how to solve the issue as typing the error in > Google shows only one link: > ( http://blog.e-shell.org/134 ) The above recode command works for the guys in the blog post you linked, as they were converting a database with Spanish data to UTF-8. They knew what encoding they were coming from. In your case, you have a mixed bag of encodings, going all the way from latin encodings to japanese. I'm not sure what recode would do to data that's in a different encoding than the specified source encoding - I expect that it will just assume it's in the specified source encoding (it cannot know that this isn't the case for a particular string) and attempt to convert it to UTF-8 _using that encoding_. Chances are you just converted valid data in a different encoding (than the source encoding you specified) into garbage in UTF-8... I seem to recall that if recode runs into problems recoding a string to UTF-8 it will leave it untouched, but that will NOT happen in all cases. Sometimes it will succeed, even though the result has no meaning to a human. That's a nasty problem you ran into, I hope the archives provide the wisdom you need. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] invalid memory alloc request size
Yesterday I had a problem on a 64-bit 9.1.1 install:
# select version();
version
PostgreSQL 9.1.1 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real
(Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit
(1 row)
The logs showed this anomaly:
2011-12-25T19:33:18+00:00 pgdb2-vpc postgres[27546]: [74474-1] ERROR: invalid
memory alloc request size 18446744073709551613
2011-12-25T19:33:18+00:00 pgdb2-vpc postgres[27546]: [74474-2] STATEMENT:
SELECT * FROM "asset_user_accesses" WHERE ("asset_user_accesses"."asset_code" =
'assignments:course_141208' AND "asset_user_accesses"."user_id" = 618503) LIMIT
1;
Googling around, it sounds like this is often due to table corruption, which
would be unfortunate, but usually seems to be repeatable. I can re-run that
query without issue, and in fact can select * from the entire table without
issue. I do see the row was updated a few minutes after this error, so is it
wishful thinking that vacuum came around and successfully removed the old,
corrupted row version?
--
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 triggers - defining a global resource (java)
Hey Alban, Thanks for the reply. I had a follow up question w.r.t listen/notify: I am planning to associate a NOTIFY with an update on a table - a trigger is associated with the update, and we execute NOTIFY in the trigger code. The NOTIFY directly goes to a remote server and contains the updated row data in the payload (serialized). Each update will result in a diffferent payload (timestamp will be in it). My question is whether LISTEN/NOTIFY was designed to handle this scenario? The update rate may scale to very high levels, and each of those updates will do a trigger call and issue a notification. Also, the size of the payload may be large (maybe 10KB per notification). I am planning to do a load test myself - but it would be great if you already know some good reason not to go ahead with this idea. Looking at the big picture, my main requirement is to communicate with a remote server about an update ASAP - its part of a very time critical workflow. I am new to Postgre (and SQL servers in general) and don't know what the best solution is. I was in Google earlier, where bigtables are used everywhere. And bigtables have a fantastic framework for asynchronous execution of code based on update on any cell of the table. I am trying to find something similar here, which really SCALES. Thanks, Aman On Sun, Dec 25, 2011 at 3:02 AM, Alban Hertroys wrote: > On 23 Dec 2011, at 14:33, Aman Gupta wrote: > > > The problem statement is mentioned here: > > > http://stackoverflow.com/questions/8615408/postgresql-triggers-defining-a-global-resource-java > > > > I am looking for the "best" solution to that problem. > > That would be using LISTEN/NOTIFY. > > If you perform RPCs directly from within your trigger, the transaction > needs to wait until the RPC call succeeded, keeping locks open much longer > than necessary. That will block other transactions from touching these rows > while the RPC is going on, among which will be autovacuum. > > For an implementation using LISTEN/NOTIFY you'd basically write a local > daemon that's polling the database with LISTEN and performs the necessary > RPC when needed. It doesn't even need to be written in Java, you could use > a language that can handle NOTIFY as an event (although in the end it > probably boils down to the same, but closer to kernel-level). > > If your PG is pre-9, then you'll want some mechanism that keeps a pool of > pending data for RPC. In 9.0 and up you can send record information along > with NOTIFY. > > Alban Hertroys > > -- > The scale of a problem often equals the size of an ego. > > >
[GENERAL] Re: Unable to connect to PostgreSQL server : Could not get socket error status
On Dec 23, 7:51 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote:
> On Friday, December 23, 2011 1:32:49 am Shankar Palaniappan wrote:
>
> > Hi John,
>
> > Thanks for your response.
>
> > I am able to connect to the database through psql command. Please find
> > the commands that I have tried through PHP and no success for past 3
> > days.
> > Please let me know whether you can help me in this issue.
>
> Some questions:
> 1) What is the connection string are you using for psql?
psql -p 5495 -d support_tool -U gls -h localhost
> 2) How are you connecting in each case(PHP,psql), remotely or locally?
I am connecting the psql from local and PHP from remote system. I was
also able to access the postgres from the same system using C program
> 3) What is listen_addresses set to in postgresql.conf?
I am running the postmaster with
postmaster -i -D /path
I am running with the following version and I dont have the privilege
to update the existing versions. listen_addresses option is not
available in the existing version
postmaster (PostgreSQL) 7.4.2
PHP 5.2.6
Today I found one issue.. where I was not able to run the postmaster
with -h option ( that is not other than localhost)
The error what I got is that it was not able to create the listening
socket.
LOG: could not bind IPv4 socket: Can't assign requested address
HINT: Is another postmaster already running on port 5496? If not,
wait a few seconds and retry.
FATAL: could not create listen socket for "IP address"
Please help me to resolve the issue. I dont have root privilege to
access the system
Thanks,
Shankar
>
>
>
>
>
>
>
> > > ini_set('track_errors','on');
> > // attempt a connection
> > $dbh = pg_connect("host=/tmp/ dbname=test_tool port=5495 user=apache
> > password=apache connect_timeout=20");
> > // $dbh = pg_connect("host=localhost port=5495 dbname=test_tool");
> > // $options = " host='localhost' port='5495' user='gls' password=''
> > dbname='test_tool' ";
> > // $dbh=pg_connect($options);
> > if (!$dbh) {
> > die("Error in connection: " . pg_last_error($dbh));
> > print_r($dbh);
> > }
>
> > else
> > {
> > echo "Connection is success";
> > }
>
> > ?>
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your
> subscription:http://www.postgresql.org/mailpref/pgsql-general- Hide quoted
> text -
>
> - Show quoted text -
--
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] Re: Unable to connect to PostgreSQL server : Could not get socket error status
On 24/12/2011 12:54, Shankar Palaniappan wrote: > On Dec 23, 7:51 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote: >> On Friday, December 23, 2011 1:32:49 am Shankar Palaniappan wrote: >> >>> Hi John, >> >>> Thanks for your response. >> >>> I am able to connect to the database through psql command. Please find >>> the commands that I have tried through PHP and no success for past 3 >>> days. >>> Please let me know whether you can help me in this issue. >> >> Some questions: >> 1) What is the connection string are you using for psql? > > psql -p 5495 -d support_tool -U gls -h localhost > >> 2) How are you connecting in each case(PHP,psql), remotely or locally? > I am connecting the psql from local and PHP from remote system. I was > also able to access the postgres from the same system using C program > >> 3) What is listen_addresses set to in postgresql.conf? > > I am running the postmaster with > > postmaster -i -D /path > > I am running with the following version and I dont have the privilege > to update the existing versions. listen_addresses option is not > available in the existing version > > postmaster (PostgreSQL) 7.4.2 > PHP 5.2.6 > > Today I found one issue.. where I was not able to run the postmaster > with -h option ( that is not other than localhost) > > The error what I got is that it was not able to create the listening > socket. > > LOG: could not bind IPv4 socket: Can't assign requested address > HINT: Is another postmaster already running on port 5496? If not, > wait a few seconds and retry. I don't know if this will help, but you have port 5496 here, and port 5495 in your psql command above. Also, PostgreSQL 7.* is VERY old, and as far as I know is no longer being maintained - so if you're being bitten by a bug, it's probably not going to get fixed. You should upgrade if you can, or move to a different service provider if you can't. 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] Re: Unable to connect to PostgreSQL server : Could not get socket error status
On Saturday, December 24, 2011 4:54:04 am Shankar Palaniappan wrote:
> On Dec 23, 7:51 pm, adrian.kla...@gmail.com (Adrian Klaver) wrote:
> > On Friday, December 23, 2011 1:32:49 am Shankar Palaniappan wrote:
> > > Hi John,
> > >
> > > Thanks for your response.
> > >
> > > I am able to connect to the database through psql command. Please find
> > > the commands that I have tried through PHP and no success for past 3
> > > days.
> > > Please let me know whether you can help me in this issue.
> >
> > Some questions:
> > 1) What is the connection string are you using for psql?
>
> psql -p 5495 -d support_tool -U gls -h localhost
>
> > 2) How are you connecting in each case(PHP,psql), remotely or locally?
>
> I am connecting the psql from local and PHP from remote system. I was
> also able to access the postgres from the same system using C program
Well in a previous post you indicated your connection strings for the PHP code
where:
$dbh = pg_connect("host=/tmp/ dbname=test_tool port=5495 user=apache
password=apache connect_timeout=20");
// $dbh = pg_connect("host=localhost port=5495 dbname=test_tool");
// $options = " host='localhost' port='5495' user='gls' password=''
dbname='test_tool' ";
The above would not work if you are trying to connect to a remote server. The
first tries to connect to a local socket and the other two are attempts to
connect to localhost. Your host would need to be the hostname(ip address) of
the host that the Postgres server is running on.
>
> I am running the postmaster with
>
> postmaster -i -D /path
>
> I am running with the following version and I dont have the privilege
> to update the existing versions. listen_addresses option is not
> available in the existing version
>
> postmaster (PostgreSQL) 7.4.2
> PHP 5.2.6
>
> Today I found one issue.. where I was not able to run the postmaster
> with -h option ( that is not other than localhost)
>
> The error what I got is that it was not able to create the listening
> socket.
>
> LOG: could not bind IPv4 socket: Can't assign requested address
> HINT: Is another postmaster already running on port 5496? If not,
> wait a few seconds and retry.
> FATAL: could not create listen socket for "IP address"
This would seem to indicate what the HINT says, that another Postgres server
is
running.
>
> Please help me to resolve the issue. I dont have root privilege to
> access the system
>
> Thanks,
> Shankar
--
Adrian Klaver
adrian.kla...@gmail.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] Error while loading sql file
Thanks for the Explaination,
I find it hard to determine the way to store data in different encodings
to store in postgresql, below is the demo of some data :-
INSERT INTO conceptnet_frame
VALUES(3884,'ja','{1}?{2}???',16,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3885,'ja','{1}?{2}??',31,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3886,'ja','{1}?{2}??',31,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3887,'ja','{1}?{2}??',31,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3888,'ja','{1}{2}???',29,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3889,'ja','{1}??{2}??',28,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3890,'ja','{1}??{2}???',14,3,2140,NULL,NULL,NULL);
INSERT INTO conceptnet_frame
VALUES(3891,'ja','{2}{1}??',9,3,2140,NULL,NULL,NULL);
Below link explains all the things :-
http://www.depesz.com/index.php/2010/03/07/error-invalid-byte-sequence-for-encoding/
Above link shows the above encoding schemes is in utf16 format but
postgresql-8.4 doesn't support it.
Is there any way to store data in different encoding in a utf-8 database.
Happy Holidays!
Alban Hertroys wrote:
On 26 Dec 2011, at 8:22, Adarsh Sharma wrote:
Dear all,
I am facing a unique issue when I try to load an sql into a postgresql database
:-
Actually, your issue isn't unique at all. You'll find it reoccurs on this list
regularly, although perhaps less frequent these days.
I faced an issue some days ago & I solved the issue by the below command :
cat backup.sql | recode iso-8859-1..u8 > backup.sql
That command assumes that every string in the sql file is encoded as iso-8859-1
(unless it already is unicode).
But this time the byte sequence changes to Japanese , & I fail to solve the
issue. Please let me know how to solve the issue as typing the error in Google
shows only one link:
( http://blog.e-shell.org/134 )
The above recode command works for the guys in the blog post you linked, as
they were converting a database with Spanish data to UTF-8. They knew what
encoding they were coming from.
In your case, you have a mixed bag of encodings, going all the way from latin
encodings to japanese.
I'm not sure what recode would do to data that's in a different encoding than
the specified source encoding - I expect that it will just assume it's in the
specified source encoding (it cannot know that this isn't the case for a
particular string) and attempt to convert it to UTF-8 _using that encoding_.
Chances are you just converted valid data in a different encoding (than the
source encoding you specified) into garbage in UTF-8... I seem to recall that
if recode runs into problems recoding a string to UTF-8 it will leave it
untouched, but that will NOT happen in all cases. Sometimes it will succeed,
even though the result has no meaning to a human.
That's a nasty problem you ran into, I hope the archives provide the wisdom you
need.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
Re: [GENERAL] Error while loading sql file
On 12/26/11 9:32 PM, Adarsh Sharma wrote: Above link shows the above encoding schemes is in utf16 format but postgresql-8.4 doesn't support it. Is there any way to store data in different encoding in a utf-8 database. afaik, anything in UTF16 can be represented by UTF8, but the inverse is not true. you'd want to convert the data to the CLIENT_ENCODING, whatever thats set to, before you send it to postgres. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
