Re: [GENERAL] [ADMIN] Issue in save and retreive file in postgres
On 06/23/2012 02:48 PM, Vishwas Dwivedi wrote: I am using postgreSQL 9.1. My problem is when I retrieve pdf file data from postgreSQL and create a pdf file in temp folder of my application. The file gets created but not able to open it, shows some error. One more thing that in my code I am converting byte array to file stream when saving and retrieving file from database. both time save and retrieve, the byte array size differs. I don't know why? Please reply to the list, not to me. Unfortunately the list doesn't set reply-to. Please provide the FULL EXACT TEXT OF THE ERROR MESSAGE by copying and pasting it into an email. See: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems As for byte array differences, which client library are you using? Is it possible it's outdated and doesn't understand bytea_format=hex ? See http://www.postgresql.org/docs/9.1/static/datatype-binary.html . -- Craig Ringer
[GENERAL] row_to_json question
How can I use row_to_json for a subset of columns in a row? (without
creating a new view or using a CTE?)
What I want returned:
{"email_address":"j...@tanga.com","username":"joevandyk"}
Note that there is no "id" column in the result.
create table users (id serial primary key, email_address varchar,
username varchar);
insert into users (email_address, username) values ('j...@tanga.com',
'joevandyk');
select row_to_json(users) from users;
{"id":1,"email_address":"j...@tanga.com","username":"joevandyk"}
Correct, except that the "id" column is in the result.
select row_to_json(row(users.email_address, users.username)) from users;
{"f1":"j...@tanga.com","f2":"joevandyk"}
The column names are incorrect.
--
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] row_to_json question
On Sat, Jun 23, 2012 at 3:03 PM, Joe Van Dyk wrote:
> How can I use row_to_json for a subset of columns in a row? (without
> creating a new view or using a CTE?)
>
> What I want returned:
> {"email_address":"j...@tanga.com","username":"joevandyk"}
> Note that there is no "id" column in the result.
>
>
> create table users (id serial primary key, email_address varchar,
> username varchar);
> insert into users (email_address, username) values ('j...@tanga.com',
> 'joevandyk');
This is the best I can come up with:
select row_to_json(f) from (select email_address, username from users) f;
{"email_address":"j...@tanga.com","username":"joevandyk"}
Is there a cleaner way to do this?
--
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] efficiency of wildcards at both ends
On 2012-06-20, Sam Z J wrote: > --0016e6d999db24c4c704c2ea7a97 > Content-Type: text/plain; charset=ISO-8859-1 > > Hi all > > I'm curious how is wildcards at both ends implemented, e.g. LIKE '%str%' > How efficient is it if that's the only search criteria against a large > table? how much does indexing the column help fulltextsearch can be abused with a custom lexer that fragments the string in every possible way and that can be matched against, but not using LIKE. Works OK on tables with tens of thaousands of rows haven't tried it on larger rows. -- ⚂⚃ 100% natural -- 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] Extensions and roles for access administration
On 2012-06-22, Виктор Егоров wrote: > --001636eeeb987f3f6204c31480e9 > Content-Type: text/plain; charset=UTF-8 > > Thanks, this clarifies things for me. > > There's DROP ROLE IF EXISTS, which I'm using. > that seems kind of pointless, as, if the role exists and owns anything it's going to fail. -- ⚂⚃ 100% natural -- 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 2012-06-19, Rafal Pietrak wrote: > And we are talking about interractive psql breaking transaction because > of syntax error - almost always this is a one time typo. I'd prefere it > to be a bit more "sloopy", then deployed SQL application (e.g. > non-interactive session). possibly you could program keyboard macros to handle savepoints to have an easy way to recover from these errors, but if you're working on a busy database keeping a transaction open whislt you think about syntax is going to cost perfromance for the other users. -- ⚂⚃ 100% natural -- 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 handling
On 2012-06-20, Little, Douglas wrote:
> --_000_8585BA53443004458E0BAA6134C5A7FBADD4CD8CEGEXCMB01owwroo_
> Content-Type: text/plain; charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> Hello,
>
> Greenplum 4.1.2.4 (PG 8.2.3)
> We are revising how we implement functions in order to better capture and h=
> andle fatal errors.
>
> What we want to have happen,
>
> 1. is to have the fatal error captured,
>
> 2. logged to our processing table,
>
> 3. then have the function & psql exit with a non-zero return code, in=
> forming Informatica of the process failure.
you're going to have to return the result as a string to the script
that calls psql and have that script generate the return code,
retval=`psql -c "copy(select funcname('arg')) to stdout"`
exit $retval
--
⚂⚃ 100% natural
--
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] row_to_json question
Also try:
https://bitbucket.org/adunstan/json_91
--Raghav
On Sun, Jun 24, 2012 at 3:45 AM, Joe Van Dyk wrote:
> On Sat, Jun 23, 2012 at 3:03 PM, Joe Van Dyk wrote:
> > How can I use row_to_json for a subset of columns in a row? (without
> > creating a new view or using a CTE?)
> >
> > What I want returned:
> > {"email_address":"j...@tanga.com","username":"joevandyk"}
> > Note that there is no "id" column in the result.
> >
> >
> > create table users (id serial primary key, email_address varchar,
> > username varchar);
> > insert into users (email_address, username) values ('j...@tanga.com',
> > 'joevandyk');
>
> This is the best I can come up with:
>
> select row_to_json(f) from (select email_address, username from users) f;
> {"email_address":"j...@tanga.com","username":"joevandyk"}
>
> Is there a cleaner way to do this?
>
> --
> 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 message "psql: could not connect to server: No such file or directory"
On 06/22/2012 03:19 PM, Stefan Schwarzer wrote: 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…. You really should be able to use pg_ctl though. I'm puzzled about why you don't appear to be looking at the server error logs (/usr/local/pgsql-9.1/data/pg_log) during troubleshooting. They're your main source of info. -- 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
[GENERAL] Hot standby streaming replication doesn't work
I am following the instructions on the wiki https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#PITR.2C_Warm_Standby.2C_Hot_Standby.2C_and_Streaming_Replication using the "10 minute" version of the setup. On the master I have postgresql.conf wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 32 wal_keep_segments = 64 pg_hba.conf host replication all 10.X.X.X/32 trust # (I set up a trust for now.. I created the user on the master) BTW the wiki should tell you to create the user. on the slave I have postgresql.conf hot_standby = on recovery.conf standby_mode = 'on' primary_conninfo = 'host=10.X.X.X port=5432 user=replication' I followed the instructions for rsyncing the files over and restarted the slave the log file says. 2012-06-24 06:51:13 UTC [25274]: [2-1] user=,db= LOG: received fast shutdown request 2012-06-24 06:51:13 UTC [25274]: [3-1] user=,db= LOG: aborting any active transactions 2012-06-24 06:51:13 UTC [25278]: [2-1] user=,db= FATAL: terminating walreceiver process due to administrator command 2012-06-24 06:51:13 UTC [25276]: [1-1] user=,db= LOG: shutting down 2012-06-24 06:51:13 UTC [25276]: [2-1] user=,db= LOG: database system is shut down 2012-06-24 06:51:15 UTC [25324]: [1-1] user=,db= LOG: database system was shut down in recovery at 2012-06-24 06:51:13 UTC 2012-06-24 06:51:15 UTC [25325]: [1-1] user=[unknown],db=[unknown] LOG: incomplete startup packet 2012-06-24 06:51:15 UTC [25324]: [2-1] user=,db= LOG: entering standby mode 2012-06-24 06:51:15 UTC [25324]: [3-1] user=,db= LOG: consistent recovery state reached at 2F/670248B0 2012-06-24 06:51:15 UTC [25324]: [4-1] user=,db= LOG: redo starts at 2F/67024820 2012-06-24 06:51:15 UTC [25324]: [5-1] user=,db= LOG: record with incorrect prev-link 2E/F1024868 at 2F/670248B0 2012-06-24 06:51:15 UTC [25323]: [1-1] user=,db= LOG: database system is ready to accept read only connections 2012-06-24 06:51:15 UTC [25328]: [1-1] user=,db= LOG: streaming replication successfully connected to primary I created a table in the master and added some rows and that table doesn't show up in the slave. I see that both the sender and the receivers are running so I am at a loss as to what is going on. Any clues? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
