Re: [GENERAL] RFE: Column aliases in WHERE clauses
On Wed, Sep 19, 2012 at 11:15 PM, David Johnston pol...@yahoo.com wrote: I could maybe see something like the following having some value: SELECT inverse FROM data WHERE x0 AND inverse .5 MACRO inverse (1/x) WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM macros WHERE x0 AND inverse .5 ChrisA -- 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_upgrade: out of memory
Hi, I have attempted to upgrade my Postgres installation this morning from 9.0.1 to 9.2.0 and it failed with an out of memory problem using pg_dumpall to dump the first database. So after backing out the change and restarting level 9.0.1, I've done some basic investigation into the failure of the 9.2.0 pg_upgrade by checking output in pg_upgrade_utility.log ... command: /opt/serviceMonitoring/postgres/bin/pg_dumpall --port 50432 --username postgres --schema-only --binary-upgrade -f pg_upgrade_dump_all.sql pg_upgrade_utility.log 21 pg_dump: out of memory pg_dumpall: pg_dump failed on database cahoot_monitoring, exiting ... rerunning pg_dump_all using existing version 9.0.1 ... /opt/serviceMonitoring/postgres/bin/pg_dumpall --port 65432 --username postgres --schema-only --binary-upgrade -f pg_upgrade_dump_all.sql ... works fine. Try with version 9.2.0 as per pg_upgrade ... /opt/serviceMonitoring/postgres_9.2/bin/pg_dumpall --port 65432 --username postgres --schema-only --binary-upgrade -f pg_upgrade_dump_all.sql pg_dump: out of memory pg_dumpall: pg_dump failed on database cahoot_monitoring, exiting ... tail -3 pg_upgrade_dump_all.sql ... \connect cahoot_monitoring ... no help there. Try with version 9.1.5 as per pg_upgrade ... cd /tmp /opt/serviceMonitoring/postgres_9.1/bin/pg_dumpall --port 65432 --username postgres --schema-only --binary-upgrade -f pg_upgrade_dump_all.sql ... works fine. Has anyone else hit this problem ? Matthew Emails aren't always secure, and they may be intercepted or changed after they've been sent. Produban doesn't accept liability if this happens. If you think someone may have interfered with this email, please get in touch with the sender another way. This message and any documents attached to it do not create or change any contract unless otherwise specifically stated. Any views or opinions contained in this message are solely those of the author, and do not necessarily represent those of Produban, unless otherwise specifically stated and the sender is authorised to do so. Produban doesn't accept responsibility for damage caused by any viruses contained in this email or its attachments. Emails may be monitored. If you've received this email by mistake, please let the sender know at once that it's gone to the wrong person and then destroy it without copying, using, or telling anyone about its contents. Produban Servicios Informaticos Generales, S.L. (UK Branch). Registered office: Shenley Wood House, Chalkdell Drive, Shenley Wood, Milton Keynes MK5 6LA. Branch registration number BR 008486. Ref:[PDB#014]
Re: [GENERAL] foreign key from array element
Hi Chris, thank you very much for taking the time to read the article and get into the features proposed with our patch. On Tue, 18 Sep 2012 17:17:56 -0700, Chris Travers chris.trav...@gmail.com wrote: So those are the cautions and why I don't think a feature like this is suitable for routine usage, but truth be told a lot of the object-relational features are definitely not for routine usage and make a mess of things if people use them just because they can. I use table inheritance and I totally understand a lot of people's hostility towards this feature. Again, anytime you break 1NF you should probably have a really good reason. I don't think this changes here. I agree with you that this feature won't (and probably shouldn't) change modelling approaches in the majority of the cases. But will bring new opportunities, therefore make PostgreSQL even more versatile. I still believe that in some cases - not just indistinctively - aggregation in object oriented modelling can definitely be logically modelled using arrays, with referential integrity guaranteed by this feature. However, after thinking about the feature overnight, I can see a number of use cases for it, ranging from recording something like race results (where update contention is definitionally not an issue because the record of an event aren't supposed to change) to sanity checks in materialized views, and there are probably additional uses that are not apparent yet. I totally agree with you. This is exactly what we (as a community) need to do now as far as this feature is concerned. We need to have a larger use base and from there fully understand what the community needs. For instance, for 9.2 we had already developed actions on update and delete operations - assuming generic use cases. We have preferred for now to take out that part and start with a simpler patch where actions are forbidden. Through community feedback we found a name for the feature that was commonly accepted (we had called them EACH FOREIGN KEYS last year), and came up with an easy to understand syntax (and a better naming). It was important not to go too far down an unexplored territory. :) So yeah, as far as the feature goes, as documented, I haven't tried it fully yet (expect to do so this weekend), but it looks useful at least in some cases. Thank you. That's really much appreciated. Cheers, Gabriel -- Gabriele Bartolini - 2ndQuadrant Italia PostgreSQL Training, Services and Support gabriele.bartol...@2ndquadrant.it - www.2ndQuadrant.it -- 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] application for postgres Log
thanx Laurenz,
But
Our CSV Log contains lot of statements like the following THREE lines. They
appear exactly one after the other.
And they number in thousands for a Session (more than ten thousand)
2011-11-11 12:41:31.484
IST,agent1,pem,524,localhost:2141,4ebccaa2.20c,754,idle,2011-11-11
12:41:30 IST,2/308,0,LOG,0,statement: INSERT INTO pemdata.settings
(name, setting, unit, server_id) VALUES ('xmlbinary', 'base64',
NULL, '1')exec_simple_query, .\src\backend\tcop\postgres.c:900,
2011-11-11 12:41:31.484
IST,agent1,pem,524,localhost:2141,4ebccaa2.20c,755,INSERT,2011-11-11
12:41:30 IST,2/0,0,LOG,0,duration: 0.000 msexec_simple_query,
.\src\backend\tcop\postgres.c:1128,
2011-11-11 12:41:31.484
IST,agent1,pem,524,localhost:2141,4ebccaa2.20c,756,INSERT,2011-11-11
12:41:30 IST,2/0,0,LOG,0,QUERY STATISTICS,! system usage stats:
! 0.00 elapsed 0.00 user 0.00 system sec
! [0.25 user 0.156250 sys total],INSERT INTO pemdata.settings
(name, setting, unit, server_id) VALUES ('xmlbinary', 'base64',
NULL, '1'),,ShowUsage, .\src\backend\tcop\postgres.c:4305,
Is there anything that we enabled, because they dont appear after that
particular session.
The log file is uploaded at
http://dl.dropbox.com/u/71964910/pg_log_with_lot_of_session.zip
arvind ps
It would indeed be divine intervention if fifty thousand had
five zeros.
Other than that, I don't see anything special about that.
A session can last pretty long.
Maybe you can solve your mystery by looking at the log entries.
They should tell you what was going on.
Although it never reoccurs and luckily we had csv option on during
that period.
Where should i report such findings
I don't think there is anything wrong.
At least nothing database related.
I have uploaded that Part of Log at
http://dl.dropbox.com/u/71964910/pg_log_with_lot_of_session.zip
That looks like somebody turned on log_statement_stats for a spell.
Yours,
Laurenz Albe
[GENERAL] should I increase default_statistics_target
Using explain analyze of a large query I found that in every step there are a lot difference between the number of rows between actual and estimated. I am using default_statistics_target 200. Should I increase it?
Re: [GENERAL] should I increase default_statistics_target
Hi, On 20 September 2012 20:49, AI Rumman rumman...@gmail.com wrote: Using explain analyze of a large query I found that in every step there are a lot difference between the number of rows between actual and estimated. I am using default_statistics_target 200. Should I increase it? I would keep it at default level but I would increase it per column: ALTER TABLE table ALTER column SET STATISTICS number and you can do the same for index: ALTER TABLE index_name ALTER COLUMN column SET STATISTICS number (for function indexes you need to use \d in order to see real column name) Finally, you need to run analyse on that table / column -- Ondrej Ivanic (ondrej.iva...@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
[GENERAL] Need psql send email
Hi All,I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server.Can any one help me in solving this?. pavithra@gmail.com -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Need psql send email
Check this article: http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/ 2012/9/20 pavithra pavithra@gmail.com Hi All, I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server. Can any one help me in solving this?. [hidden email]http://user/SendEmail.jtp?type=nodenode=5724700i=0 -- Victor Y. Yegorov
Re: [GENERAL] Need psql send email
On 20/09/2012 13:07, pavithra wrote: Hi All, I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server. Can any one help me in solving this?. [hidden email] Hi there, It's not possible to send email directly from pl/pgsql; it might be possible in the untrusted form of pl/perl, but I'm not sure. A possible alternative would be to have an external process poll a queue table, take its data from there and send the emails. HTH, 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] Need psql send email
On Thu, Sep 20, 2012 at 05:07:18AM -0700, pavithra wrote: Hi All,I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server.Can any one help me in solving this?. pavithra@gmail.com http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Need psql send email
I am more wondered where we need to give the port address and smtpserver. Can you give me the details of these?. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700p5724705.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
Re: [GENERAL] Need psql send email
Hi All,I am new to postgresql. I want to send email by using pl
pgsql. I want
to know how to set up the configurations for mail server.Can any one
help me
in solving this?. pavithra@gmail.com
http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/
Best regards,
depesz
Alternatively:
CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to text, p_subject
text, p_content text)
RETURNS void AS
$BODY$
use strict;
use warnings;
my ($from, $to, $subject, $content) = @_;
open(MAIL, |/usr/sbin/sendmail -t) or die 'Cannot send mail';
print MAIL From: $from\n;
print MAIL To: $to\n;
print MAIL Subject: $subject\n\n;
print MAIL $content;
close(MAIL);
$BODY$
LANGUAGE plperlu;
Works ok provided sendmail is configured.
or:
CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,
p_from text,
p_to text,
p_subject text,
p_content text,
p_timeout integer DEFAULT 60,
p_debug integer DEFAULT 0,
p_exactaddr integer DEFAULT 1,
p_skipbad integer DEFAULT 1)
RETURNS void AS
$BODY$
use strict;
use warnings;
use Net::SMTP;
no strict 'refs';
my ($host, $sender, $recipient, $subject, $body, $timeout, $debug, $exact,
$skipbad) = @_;
(!defined($host) || !($host)) die 'No SMTP host provided.';
(!defined($sender) || !($sender)) die 'No sender address/name
provided.';
(!defined($recipient) || !($recipient)) die 'No recipient address
specified.';
my $mail = Net::SMTP-new(
Host = $host,
Debug = $debug,
Timeout = $timeout,
ExactAddresses = $exact
) or die 'Net::SMTP-new() Failed';
$mail-mail($sender);
$mail-recipient($recipient, { SkipBad = $skipbad });
$mail-data();
$mail-datasend(MIME-Version: 1.0\n);
$mail-datasend(From: . $sender . \n);
$mail-datasend(To: . $recipient . \n);
$mail-datasend(Reply-To: . $sender . \n);
$mail-datasend(Subject: . $subject . \n\n);
$mail-dataend();
$mail-quit();
$BODY$
LANGUAGE plperlu;
Feel free to hack away as much as required.
Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.
Cheers
Martin
=
Romax Technology Limited
Rutherford House
Nottingham Science Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=
Confidentiality Statement
This transmission is for the addressee only and contains information that
is confidential and privileged.
Unless you are the named addressee, or authorised to receive it on behalf
of the addressee
you may not copy or use it, or disclose it to anyone else.
If you have received this transmission in error please delete from your
system and contact the sender. Thank you for your cooperation.
=
Re: [GENERAL] Passing row set into PL/pgSQL function.
On 09/20/2012 01:47 PM, Lucas Clemente Vella wrote:
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
I have already seen this page, I am OK in running SERIALIZABLE
transactions, and have no problem in replaying failed transactions due
to race condition. Anyway, that is completely off my issue: I need
upsert and I am prepared to deal with it. I just want to save typing
by creating a reusable function.
In that case, maybe you could have your function accept a `refcursor`?
DECLARE some_curs CURSOR FOR VALUES ('a',1), ('b',2), ('c',3);
SELECT funky_upsert('table', ARRAY['col1','col2'], 'some_curs');
CLOSE some_curs;
Internally it could fetch rows from the refcursor into record fields and
do what it needed.
Personally I'd just do the work app-side.
--
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
Re: [GENERAL] RFE: Column aliases in WHERE clauses
On Wed, Sep 19, 2012 at 11:15 PM, David Johnston pol...@yahoo.com wrote: I could maybe see something like the following having some value: SELECT inverse FROM data WHERE x0 AND inverse .5 MACRO inverse (1/x) WITH macros AS (SELECT *,1/x AS inverse FROM data) SELECT inverse FROM macros WHERE x0 AND inverse .5 In your example the macro has to either be attached directly to the FROM or be used as part of a sub-select; it is not a text substitution macro at all. The pre-processor upon encountering a macro, would simply replace all identifiers (at the same level in the query) with (expression). David J. -- 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] Need psql send email
On 09/20/2012 08:40 PM, Martin French wrote: Both of these work fine provided PL/PerlU is installed and the server is properly configured on the network, and that there is a valid SMTP mail host to receive. The 1st one seems OK in a scary-from-a-security-standpoint kind of way. The 2nd, not so much. See http://stackoverflow.com/questions/12002662/psql-trigger-send-email Imagine if the DNS goes wonky. Do you want all your backends tied up in DNS lookups? Or timing-out TCP connections? BTW, pavithra, check out http://brandolabs.com/pgmail if you really want to do it in the database. -- 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
Re: [GENERAL] Need psql send email
many is the time when spammers have used Open Relay SMTP servers to send their
junk mail so i would advise against using sendmail on Open Relay SMTP servers
I would narrow access by SSH or open a secure tunnel thru your firewall to your
own internal DatabaseManagementSystem/J2EEServer/ApacheHTTPServer then allow
those scripts (PL-SQL or Perl or Java) to invoke sendmail to the SMTPMailServer
inside the firewall
If you are sponsoring your own email-server and I hope you are DISALLOW OPEN
RELAY
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
To: dep...@depesz.com
CC: pavithra@gmail.com; pgsql-general@postgresql.org;
pgsql-general-ow...@postgresql.org
Subject: Re: [GENERAL] Need psql send email
From: martin.fre...@romaxtech.com
Date: Thu, 20 Sep 2012 13:40:58 +0100
Hi All,I am new to postgresql. I want to send email by using
pl
pgsql. I want
to know how to set up the configurations for mail server.Can
any one help me
in solving this?. pavithra@gmail.com
http://www.depesz.com/2012/06/13/how-to-send-mail-from-database/
Best regards,
depesz
Alternatively:
CREATE OR REPLACE FUNCTION sendmail(p_from text, p_to
text, p_subject text, p_content text)
RETURNS void AS
$BODY$
use strict;
use warnings;
my ($from, $to, $subject, $content) = @_;
open(MAIL, |/usr/sbin/sendmail -t) or
die 'Cannot send mail';
print MAIL From: $from\n;
print MAIL To: $to\n;
print MAIL Subject: $subject\n\n;
print MAIL $content;
close(MAIL);
$BODY$
LANGUAGE plperlu;
Works ok provided sendmail is configured.
or:
CREATE OR REPLACE FUNCTION send_smtp(p_mail_host text,
p_from text,
p_to text,
p_subject text,
p_content text,
p_timeout integer
DEFAULT 60,
p_debug integer
DEFAULT 0,
p_exactaddr integer
DEFAULT 1,
p_skipbad integer
DEFAULT 1)
RETURNS void AS
$BODY$
use strict;
use warnings;
use Net::SMTP;
no strict 'refs';
my ($host, $sender, $recipient, $subject, $body, $timeout,
$debug, $exact, $skipbad) = @_;
(!defined($host) || !($host)) die 'No SMTP
host provided.';
(!defined($sender) || !($sender)) die
'No sender address/name provided.';
(!defined($recipient) || !($recipient))
die 'No recipient address specified.';
my $mail = Net::SMTP-new(
Host = $host,
Debug = $debug,
Timeout = $timeout,
ExactAddresses = $exact
) or die 'Net::SMTP-new()
Failed';
$mail-mail($sender);
$mail-recipient($recipient, { SkipBad = $skipbad
});
$mail-data();
$mail-datasend(MIME-Version: 1.0\n);
$mail-datasend(From: . $sender . \n);
$mail-datasend(To: . $recipient .
\n);
$mail-datasend(Reply-To: . $sender
. \n);
$mail-datasend(Subject: . $subject
. \n\n);
$mail-dataend();
$mail-quit();
$BODY$
LANGUAGE plperlu;
Feel free to hack away as much as required.
Both of these work fine provided PL/PerlU is installed and the server is
properly configured on the network, and that there is a valid SMTP mail
host to receive.
Cheers
Martin
=
Romax Technology Limited
Rutherford House
Nottingham Science Technology Park
Nottingham,
NG7 2PZ
England
Telephone numbers:
+44 (0)115 951 88 00 (main)
For other office locations see:
http://www.romaxtech.com/Contact
=
===
E-mail: i...@romaxtech.com
Website: www.romaxtech.com
=
Confidentiality Statement
This transmission is for the addressee only and contains
Re: [GENERAL] Slow counting still true?
Em 18/09/2012 15:24, Jeff Janes escreveu: On Mon, Sep 17, 2012 at 9:14 AM, Edson Richter edsonrich...@hotmail.com wrote: The wiki page in question has been updated today, and I see the alert in top of page Note that the following article only applies to versions of PostgreSQL prior to 9.2. Index-only scans are now implemented. So seems that traversing indexes for count(*) would be faster on 9.2, right? Not really, as it still needs to visit some representation of every tuple. Now, if the entire index in is RAM while the table would not be, it could be a lot faster. But that is more of a special case than a general one. AFAIK, for count(*) doesn't matter the order data is stored - just need to load index leaf pages and count from there, right? That would only work if there was no concurrent activity. If someone else splits on index page, some of the entries on that page could move to a location where they would get visited either zero times or two times. I see. This is were MS SQL Server escalates row locks into page locks, and get rid of the concurrency (at very expensive cost, IMHO). Regards, Edson Cheers, Jeff -- 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] Need psql send email
The 1st one seems OK in a scary-from-a-security-standpoint kind of way. Agree, it needs to be weighed up and assessed from a security stand point I guess. The 2nd, not so much. See http://stackoverflow.com/questions/12002662/psql-trigger-send-email Imagine if the DNS goes wonky. Do you want all your backends tied up in DNS lookups? Or timing-out TCP connections? Agree 100%, which is why I noted: the server is properly configured on the network... I suppose you could always provide an IP address as the mail host. This function is only a Quick Knock Together job, that works readily enough. IMHO There's always an inherent risk with any form of sending mail from an RDBMS, whether it be abuse or otherwise, however; it's one of those situations where needs must, and more often than not must be done quickly. I would guess that having SMTP built into the DB engine itself would be no less susceptible to abuse or problems than any other method (For example UTL_SMTP in Oracle, which I've had hang before due to issues with SMTP servers). I guess it's one of those where you just have to weigh up the options and choose the best one for your situation/application. Cheers Martin = Romax Technology Limited Rutherford House Nottingham Science Technology Park Nottingham, NG7 2PZ England Telephone numbers: +44 (0)115 951 88 00 (main) For other office locations see: http://www.romaxtech.com/Contact = === E-mail: i...@romaxtech.com Website: www.romaxtech.com = Confidentiality Statement This transmission is for the addressee only and contains information that is confidential and privileged. Unless you are the named addressee, or authorised to receive it on behalf of the addressee you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please delete from your system and contact the sender. Thank you for your cooperation. =
Re: [GENERAL] Need psql send email
Em 20/09/2012 09:07, pavithra escreveu: Hi All, I am new to postgresql. I want to send email by using pl pgsql. I want to know how to set up the configurations for mail server. Can any one help me in solving this?. [hidden email] /user/SendEmail.jtp?type=nodenode=5724700i=0 View this message in context: Need psql send email http://postgresql.1045698.n5.nabble.com/Need-psql-send-email-tp5724700.html Sent from the PostgreSQL - general mailing list archive http://postgresql.1045698.n5.nabble.com/PostgreSQL-general-f1843780.html at Nabble.com. Dear friend, I don't know if it is possible. But my experience with MS SQL Server (integration with OutLook) introduces hundreds of flaws (including crashes) into the database. How did I accomplish this task: a) To notify backups and so, I've configured my Cron task to do that (it is fairly easy and well documented) b) To notify about business tasks of my applications, my applications send the e-mail (in my case, I do use Java, so I use standard JavaMail API that does everything in a snap without any flaws for years now). I believe every language in the world has similar stable APIs for sending e-mails c) If I need to send e-mail based on database events (like a trigger), I use a Queue Table where I insert messages that need to be sent, and have external application that (from time to time) checks this table for new messages to be sent. I hope this ideas help you. Regards, Edson.
Re: [GENERAL] Need psql send email
Hi all; A couple points here. First, you probably don't want to send email directly from a database function. This gives significant problems for which there is no good solution. Consider: 1) You sent your email and now the transaction rolls back. You *cannot* roll back the sent email. 2) Your email fails to send. Do you abort the transaction? IMO it is always better to send email from a second process that can be notified on db commit. This avoids these issues and kicks them to a post-transaction handler. As luck would have it, I recently set a project up on Google Code to help address this (and other application integration) issues. See http://code.google.com/p/pg-message-queue/ There isn't a lot of overlap with something like pgq. This is listen/notify/queue tables based. May not ever be big and professional but it should work once the bugs are ironed out. Even before then it may give a good idea of how to implement a notification-based queue on PostgreSQL. The idea here is that you can essentially send a message to a channel on a db event (say, from a trigger) and then have another app that either periodically checks the queue (say, from a cron job) or listens on a channel for notifications. The whole thing was confirmed working before I made some changes. If folks are interested in helping I am sure it will be well tested and working in no time. Please read the docs first though. I wouldn't say it is production-ready yet, but it may provide an overview of how to go about implementing something like this in production. Also for more info on how to do this with a LISTEN/NOTIFY approach outside of the above, see http://ledgersmbdev.blogspot.com/2012/09/objectrelational-interlude-messaging-in.html In general I think mixing transactional and non-transactional side-effects is just asking for trouble. Don't do it any more than you have to. Best Wishes, Chris Travers
Re: [GENERAL] pg_upgrade: out of memory
Carrington, Matthew (Produban) matthew.carring...@produban.co.uk writes: I have attempted to upgrade my Postgres installation this morning from 9.0.1 to 9.2.0 and it failed with an out of memory problem using pg_dumpall to dump the first database. Hm. I'm not aware of any reason for 9.2 pg_dump to take hugely more memory than 9.0. How big is the database (how many objects)? When you run 9.0 pg_dump against it, how big does the process get? (Watching it in top is probably a close enough answer here.) 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] WITH RECURSIVE from 2 or more tables.
Hello. I have 2 tables: CREATE TABLE group ( id serial NOT NULL name character varying(23) NOT NULL id_user integer NOT NULL DEFAULT 0, parent integer DEFAULT 0, CONSTRAINT group_user_fkey FOREIGN KEY (id_user) REFERENCES user (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE, ) and CREATE TABLE user ( id serial NOT NULL login character varying(12) NOT NULL parent integer DEFAULT 0 ) Can I get a tree of records in a single query, and their two tables using WITH RECURSIVE. tree one table I made, but how to combine these queries do not understand. WITH RECURSIVE gg(id,parent,level,path,cycle) AS ( SELECT id,parent,0,ARRAY[id],false FROM web.group WHERE id=899 UNION ALL SELECT g.id,g.parent,level + 1,path||g.id,g.id=ANY(path) FROM web.group as g,gg WHERE g.parent=gg.id AND NOT cycle ) SELECT u.id,u.name,path FROM web.group as u, gg WHERE gg.id=u.id; WITH RECURSIVE uu(id,parent,level,path,cycle) AS ( SELECT id,id_user,0,ARRAY[id],false FROM web.user WHERE id=71 UNION ALL SELECT u.id,u.id_user,level + 1,path||u.id,u.id=ANY(path) FROM web.user as u,uu WHERE u.id_user=uu.id AND NOT cycle ) SELECT u.id,u.login,path FROM web.user as u, uu WHERE uu.id=u.id; Could you please help me. -- 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] Passing row set into PL/pgSQL function.
On Wed, Sep 19, 2012 at 4:37 PM, Lucas Clemente Vella lve...@gmail.com wrote:
I am trying to write a generic upsert function in PL/pgSQL, in a way
that I can specify the table were I want to insert/update, the columns
whose values I want to specify, and the values to be inserted.
So far I have come up with a solution whose signature is:
CREATE OR REPLACE FUNCTION upsert(IN tname text, IN cnames text[],
VARIADIC vals anyarray) RETURNS void
Whose tname is the table, cnames are the columns ans vals the values.
The problem I have is when I try to call the function: I can only pass
values of a previously defined type, like:
SELECT upsert('my_table', ARRAY['key', 'data'], (10,
'hello')::my_table, (20, 'world')::my_table);
Instead of:
SELECT upsert('my_table', ARRAY['key', 'data'], (10, 'hello'), (20, 'world'));
What gives me the error:
ERROR: PL/pgSQL functions cannot accept type record[]
note, pl/pgsql functions can take arrays of non-anonymous record types
-- either tables, or composite types. you're just not allowed to pass
anonymous rows in.
for key value pairs, also you should take a look at hstore. You can
also make arrays of hstore.
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using psql -f to load a UTF8 file
I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My
database is UTF8. I use psql -f to load files containing DDL and DML commands.
I discovered a long time ago that psql does not like UTF8 files: it complains
about the byte order mark on the first line. Up to now I have worked round that
by making sure that the files were saved as what Microsoft calls ANSI.
However, that option is not available if I want to insert data which includes
non-ASCII characters.
I have found a suggestion that psql can be told to expect UTF8 files by
creating a file called psqlrc.conf containing the line \encoding unicode. I
have tried putting this file (i) in the Postgres data directory, along with
postgresql.conf, and (ii) in %APPDATA%\postgresql, but I still get an error:
psql:120919_insert_into_letter.sql:1: ERROR: syntax error at or near insert
LINE 1: insert into LETTER_VAR (var_name, type) values ('REPORT_COP...
I have found a workaround, which is to start the file with a line containing
just a semicolon. Then the empty statement fails, but the other statements
succeed:
psql:120919_insert_into_letter_copy2.sql:1: ERROR: syntax error at or near 
LINE 1: ;
^
INSERT 0 1
INSERT 0 1
However, I feel sure that there must be a better way.
Ihave noted BUG report #6271: psql -f reporting unexpected syntax errors on
first command. This involves the same problem. Álvaro Herrera advised that You
need to remove [the byte order mark] before passing the file to psql. But how
am I supposed to remove the byte order mark from a UTF8 file? I thought that
the whole point of the byte order mark was to tell programs what the file
encoding is. Other programs, such as Python, rely on this.
Re: [GENERAL] Passing row set into PL/pgSQL function.
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ I have already seen this page, I am OK in running SERIALIZABLE transactions, and have no problem in replaying failed transactions due to race condition. Anyway, that is completely off my issue: I need upsert and I am prepared to deal with it. I just want to save typing by creating a reusable function. -- Lucas Clemente Vella lve...@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] Using psql -f to load a UTF8 file
Alan Millington admilling...@yahoo.co.uk writes: I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My database is UTF8. I use psql -f to load files containing DDL and DML commands. I discovered a long time ago that psql does not like UTF8 files: it complains about the byte order mark on the first line. Up to now I have worked round that by making sure that the files were saved as what Microsoft calls ANSI. However, that option is not available if I want to insert data which includes non-ASCII characters. FWIW, psql 9.0 and later will ignore an initial BOM if the client encoding is UTF8. 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] Using psql -f to load a UTF8 file
Torsdag 20. september 2012 19.27.22 skrev Alan Millington :
Thank you for the link. I am using Notepad, which inserts the byte order
mark. Following the links a bit further, I gather that the version of
Notepad that I am using may not identify a UTF8 file correctly if the byte
order mark is omitted. Also, as I mentioned, Python makes use of it. (From
the Python documentation on Encoding declarations: If the first bytes of
the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file
encoding is UTF-8 (this is supported, among others, by Microsoft’s
Notepad).)
The conclusion seems to be that I must use one editor for Python, and
another for Postgres.
It's been a long time since I last wrote a Python script, but I've always used
the explicit encoding directive:
#! /usr/bin/env python
# -*- encoding: utf-8 -*-
See http://docs.python.org/release/2.5.1/ref/encodings.html which also
mentions the BOM method as an alternative.
regards, Leif
--
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] Using psql -f to load a UTF8 file
On 09/20/2012 10:44 AM, Leif Biberg Kristensen wrote:
Torsdag 20. september 2012 19.27.22 skrev Alan Millington :
Thank you for the link. I am using Notepad, which inserts the byte order
mark. Following the links a bit further, I gather that the version of
Notepad that I am using may not identify a UTF8 file correctly if the byte
order mark is omitted. Also, as I mentioned, Python makes use of it. (From
the Python documentation on Encoding declarations: If the first bytes of
the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file
encoding is UTF-8 (this is supported, among others, by Microsoft’s
Notepad).)
The conclusion seems to be that I must use one editor for Python, and
another for Postgres.
I would strongly advise against using Notepad for any kind of text
editing. Wordpad works better, or even better yet Notepad ++:
http://notepad-plus-plus.org/
It's been a long time since I last wrote a Python script, but I've always used
the explicit encoding directive:
#! /usr/bin/env python
# -*- encoding: utf-8 -*-
See http://docs.python.org/release/2.5.1/ref/encodings.html which also
mentions the BOM method as an alternative.
regards, Leif
--
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] Using psql -f to load a UTF8 file
Torsdag 20. september 2012 16.56.16 skrev Alan Millington : psql. But how am I supposed to remove the byte order mark from a UTF8 file? I thought that the whole point of the byte order mark was to tell programs what the file encoding is. Other programs, such as Python, rely on this. http://en.wikipedia.org/wiki/Byte_order_mark While the Byte Order Mark is important for UTF-16, it's totally irrelevant to the UTF-8 encoding. Still you'll find several editors that automatically input BOMs in every text file. There is usually a setting Insert Byte Order Mark somewhere in the configuration, and it may be on by default. regards, Leif -- 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] Using psql -f to load a UTF8 file
On 09/20/12 7:56 AM, Alan Millington wrote: I discovered a long time ago that psql does not like UTF8 files: it complains about the byte order mark on the first line. in case it wasn't clear from previous replies, Windows native Unicode format is NOT UTF8, its UTF16, where every character is stored as 2 bytes. -- 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
[GENERAL] Why do I have holes in my pages?
Why do I have holes in my pages? Postgres 8.4.12 select ctid from big_table on my master shows that pages have holes in them. Here is example for page 431665: (431665,2) (431665,5) (431665,8) (431665,11) (431665,14) (431665,17) (431665,20) (431665,23) Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem). So what happened to rows 1, 3 and 4 and so on? I have to size a database server for next year's budget, and I will have to explain to my mgmt why we have 400 GB taking up 2.7 TB. Help? Would appreciate a pointer to the appropriate section in the manual if this is documented. Thanks, -at -- 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] Why do I have holes in my pages?
In response to Aleksey Tsalolikhin atsaloli.t...@gmail.com: Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem). I expect that the first thing that others are going to ask is what is telling you that your DB is 400G? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Why do I have holes in my pages?
On Thu, Sep 20, 2012 at 12:34 PM, Bill Moran wmo...@potentialtech.com wrote: In response to Aleksey Tsalolikhin atsaloli.t...@gmail.com: Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem). I expect that the first thing that others are going to ask is what is telling you that your DB is 400G? Right on. I got that out of my pgstatspack report. \l+ in psql tells me the same thing - 400 GB -- 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] Why do I have holes in my pages?
Take a look at this part of the documentation: http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY The “missing” entries belong to the tuples that you have DELETEd/UPDATEd and that are no longer visible to your current session, but still might be for the others, that started some time ago. When tuples are no longer needed, VACUUM will “release” the slots by adding them into the FreeSpaceMap. Still, if you have “empty” slots in the middle of your datafiles, VACUUM cannot resize files. This leads to the fact that while database size is being not so big, actual disk space occupied by it is bigger. This is called “bloat”. Check the output of the query here: http://wiki.postgresql.org/wiki/Show_database_bloat Also, having such a big difference in the reported and actual size of the database, may I ask: - when was the last time you performed VACUUM? - don't you have autovacuum = on (which is default) in your configuration? 2012/9/20 Aleksey Tsalolikhin atsaloli.t...@gmail.com On Thu, Sep 20, 2012 at 12:34 PM, Bill Moran wmo...@potentialtech.com wrote: In response to Aleksey Tsalolikhin atsaloli.t...@gmail.com: Current DB size is 400 GB and it takes up 2.7 TB (on a 6.6 TB filesystem). I expect that the first thing that others are going to ask is what is telling you that your DB is 400G? Right on. I got that out of my pgstatspack report. \l+ in psql tells me the same thing - 400 GB -- Victor Y. Yegorov
Re: [GENERAL] Why do I have holes in my pages?
On 09/20/12 1:34 PM, Aleksey Tsalolikhin wrote: Right on. I got that out of my pgstatspack report. \l+ in psql tells me the same thing - 400 GB it might be interesting to see the output of... du -hs $PGDATA/* (assuming this is a linux or similar unix system). This will show how much space is being used by the various directories under the PG data directory.if pg_xlog is very large, you may have an issue with wal archiving or something. if pg_log is very large, you may have an issue with A) too much being logged, and B) nothing cleaning up stale log files. how did you arrive at the 2.7TB number?and what file system does this 6.6TB volume use? -- 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
Re: [GENERAL] Why do I have holes in my pages?
On Thu, Sep 20, 2012 at 1:53 PM, John R Pierce pie...@hogranch.com wrote: On 09/20/12 1:34 PM, Aleksey Tsalolikhin wrote: Right on. I got that out of my pgstatspack report. \l+ in psql tells me the same thing - 400 GB it might be interesting to see the output of... du -hs $PGDATA/* Well, that was it! Thanks, John! 2.3T/data/backups 400G/data/base We store our pg_dumps on the same filesystem (they are copied off to another server but we don't delete them) so it swelled the filesystem size as reported by df. Sorry about that. And thanks for the help! Aleksey -- 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] Using psql -f to load a UTF8 file
On 09/20/2012 11:44 PM, Leif Biberg Kristensen wrote: Torsdag 20. september 2012 16.56.16 skrev Alan Millington : psql. But how am I supposed to remove the byte order mark from a UTF8 file? I thought that the whole point of the byte order mark was to tell programs what the file encoding is. Other programs, such as Python, rely on this. http://en.wikipedia.org/wiki/Byte_order_mark While the Byte Order Mark is important for UTF-16, it's totally irrelevant to the UTF-8 encoding. I strongly disagree. The BOM provides a useful and standard way to differentiate UTF-8 encoded text files from the random pile of encodings that any given file could be. On many platforms (including all Windows versions) the default system text encoding for 8-bit text is not UTF-8. On such systems, a BOM in a UTF-8 file allows a program/editor to reliably work out that it's UTF-8 and treat it as such, rather than mangling it by interpreting it as the local system encoding. psql should accept UTF-8 with BOM. -- 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
Re: [GENERAL] Using psql -f to load a UTF8 file
On Fri, Sep 21, 2012 at 11:21 AM, Craig Ringer ring...@ringerc.id.au wrote: I strongly disagree. The BOM provides a useful and standard way to differentiate UTF-8 encoded text files from the random pile of encodings that any given file could be. The only reliable way to ascertain the encoding of a hunk of data is with something out-of-band. Relying on the first three bytes being \xEF\xBB\xBF is not much more reliable than detecting based on octet frequency, which is what leads to the Bush hid the facts hack in Notepad. This is why many Internet protocols have metadata carried along with the file (eg Content-type in HTTP), rather than relying on internal evidence. psql should accept UTF-8 with BOM. However, this I would agree with. It's cheap enough to detect, and aside from arbitrarily trying to kill Notepad (which won't happen anyway), there's not a lot of reason to choke on the BOM. But it's not a big deal. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Expression to construct a anonymous record with named columns?
Hello, Is there a way to construct write an expression that constructs a record with with named columns. Specificially without the need for a corresponding named type. That is postgres=# select row(1, 2, 3); row - (1,2,3) (1 row) Creates a unnamed record type. And indeed it is for example not possible to expand it: postgres=# select (row(1, 2, 3)).*; ERROR: record type has not been registered On the other hand columns listed in a multi column select clause create a row type that is expandable and named: postgres=# select ((bar.*).x).a from (select x from (select 1 as a, 2 as b) x) bar; a --- 1 (1 row) But it seems to not be possible to do so without a from clause: postgres=# select ((select x from (select 1 as a, 2 as b) x)).a; ERROR: syntax error at or near . LINE 1: select ((select x from (select 1 as a, 2 as b) x)).a; ^ postgres=# select ((select x from (select 1 as a, 2 as b) x)).*; ERROR: syntax error at or near . LINE 1: select ((select x from (select 1 as a, 2 as b) x)).*; So named anonymous records / row types seem to be strangely second class. Can somebody clarify the restrictions and rationale or even better show a way to do the equivalent of (made up syntax ahead): select row(1 as a, 2 as b); Cheers, Bene
Re: [GENERAL] Using psql -f to load a UTF8 file
Thank you for the link. I am using Notepad, which inserts the byte order mark.
Following the links a bit further, I gather that the version of Notepad that I
am using may not identify a UTF8 file correctly if the byte order mark is
omitted. Also, as I mentioned, Python makes use of it. (From the Python
documentation on Encoding declarations: If the first bytes of the file are the
UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared file encoding is UTF-8
(this is supported, among others, by Microsoft’s Notepad).)
The conclusion seems to be that I must use one editor for Python, and another
for Postgres.
From: Leif Biberg Kristensen l...@solumslekt.org
To: Postgres general mailing list pgsql-general@postgresql.org
Cc: Alan Millington admilling...@yahoo.co.uk
Sent: Thursday, 20 September 2012, 16:44
Subject: Re: [GENERAL] Using psql -f to load a UTF8 file
Torsdag 20. september 2012 16.56.16 skrev Alan Millington :
psql. But how am I supposed to remove the byte order mark from a UTF8
file? I thought that the whole point of the byte order mark was to tell
programs what the file encoding is. Other programs, such as Python, rely
on this.
http://en.wikipedia.org/wiki/Byte_order_mark
While the Byte Order Mark is important for UTF-16, it's totally irrelevant to
the UTF-8 encoding. Still you'll find several editors that automatically input
BOMs in every text file. There is usually a setting Insert Byte Order Mark
somewhere in the configuration, and it may be on by default.
regards, Leif
Re: [GENERAL] foreign key from array element
On Thu, Sep 20, 2012 at 12:18 AM, Gabriele Bartolini gabriele.bartol...@2ndquadrant.it wrote: Hi Chris, thank you very much for taking the time to read the article and get into the features proposed with our patch. You are welcome. Also in case there is ambiguity, the feature I was describing animosity towards was table inheritance. I have seen people advocate getting rid of the feature altogether but it is really useful for a set of problems out there. The problem of course is that in its current form it is a bit of a dangerous feature. I agree with you that this feature won't (and probably shouldn't) change modelling approaches in the majority of the cases. But will bring new opportunities, therefore make PostgreSQL even more versatile. I still believe that in some cases - not just indistinctively - aggregation in object oriented modelling can definitely be logically modelled using arrays, with referential integrity guaranteed by this feature. BTW, I don't know if you have seen the series I have been doing on Object-Relational modelling in PostgreSQL but if you haven't, http://ledgersmbdev.blogspot.com/ (right now there are 9 posts up with an epilogue coming). I cover a lot of dangerous features--- composite types in columns, non-1NF designs, table inheritance. Particularly the nested storage post might be interesting in terms of both uses and misuses of this proposed feature. In fact it occurs to me that the main thing it buys is an ability to do subset constraints on the foreign key set gracefully, for example, ensuring that there are between 5 and 10 foreign keys referenced in a specific case or the like. However, after thinking about the feature overnight, I can see a number of use cases for it, ranging from recording something like race results (where update contention is definitionally not an issue because the record of an event aren't supposed to change) to sanity checks in materialized views, and there are probably additional uses that are not apparent yet. I totally agree with you. This is exactly what we (as a community) need to do now as far as this feature is concerned. We need to have a larger use base and from there fully understand what the community needs. For instance, for 9.2 we had already developed actions on update and delete operations - assuming generic use cases. We have preferred for now to take out that part and start with a simpler patch where actions are forbidden. Through community feedback we found a name for the feature that was commonly accepted (we had called them EACH FOREIGN KEYS last year), and came up with an easy to understand syntax (and a better naming). It was important not to go too far down an unexplored territory. :) I think the problem for the cascade and set null operations is determining the behavior to be defined. would ON DELETE CASCADE delete the value from the array or would it delete the whole row? What about ON DELETE SET NULL? Do we change the value in the array to NULL or just remove it from the array? So I think for now that's sane. I think in terms of community, the object-relational features do need more exposure, and more attention generally. Part of the reason I started blogging about them was to bring more attention to them, and try to help get more exposure to the current costs and benefits of using them. If people are pushing the boundaries a bit more, I think a lot of things will get improved upon. Best Wishes, Chris Travers
Re: [GENERAL] Using psql -f to load a UTF8 file
On 09/20/12 10:27 AM, Alan Millington wrote:
I am using Notepad, which inserts the byte order mark. Following the
links a bit further, I gather that the version of Notepad that I am
using may not identify a UTF8 file correctly if the byte order mark is
omitted. Also, as I mentioned, Python makes use of it. (From the
Python documentation on Encoding declarations: If the first bytes of
the file are the UTF-8 byte-order mark ('\xef\xbb\xbf'), the declared
file encoding is UTF-8 (this is supported, among others, by
Microsoft’s Notepad).)
I've never seen Notepad generate UTF8. Usually its either 8 bit ASCII
(ISO8559-1 or something), or its UTF16 aka Unicode.
--
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
Re: [GENERAL] Using psql -f to load a UTF8 file
On Fri, Sep 21, 2012 at 2:39 PM, John R Pierce pie...@hogranch.com wrote:
On 09/20/12 10:27 AM, Alan Millington wrote:
I am using Notepad, which inserts the byte order mark. Following the links
a bit further, I gather that the version of Notepad that I am using may not
identify a UTF8 file correctly if the byte order mark is omitted. Also, as I
mentioned, Python makes use of it. (From the Python documentation on
Encoding declarations: If the first bytes of the file are the UTF-8
byte-order mark ('\xef\xbb\xbf'), the declared file encoding is UTF-8 (this
is supported, among others, by Microsoft’s Notepad).)
I've never seen Notepad generate UTF8. Usually its either 8 bit ASCII
(ISO8559-1 or something), or its UTF16 aka Unicode.
Those are the defaults; you can tell it to save as UTF-8.
But the general advice is: Don't use Notepad! It can't handle Unix
newlines either (something which annoys me periodically when I'm on a
borrowed Windows machine and need to view a file quickly). There are
many better editors around; Notepad++ was mentioned, and NoteTab is
another good one. My personal preference is SciTE, available for Linux
as well as Windows. You'll start to realize how handy syntax
highlighting is when your next bug is caught even before you save,
because the apostrophe in the quoted string breaks the colorization.
Anything that reduces debugging time can't be a bad thing!
ChrisA
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
