Webb Sprague wrote:
Hi all,
Is there a way to determine the pid of a database connection from
within that connection?
As a hypothetical example, I would like to be able to do the following:
$ps x
PID TTY STAT TIME COMMAND
11674 ?S 0:00 sshd: [EMAIL PROTECTED]/1
11675 pts/
I don't an answer to your question, but an obvious difference is that
the "slow" query contains many more loops. (this may already have been
noted, I didn't see it posted however).
(showing just the loops with more than one loop)
-> Index Scan using assemblies_pkey on assemblies a (cost=0.00
Steve Clark wrote:
function from 7.4.x postgres
CREATE FUNCTION update_dns(text, text) RETURNS integer
AS 'UPDATE domain_details SET domain = $2 WHERE domain = $1;
DELETE from domains where domain = $1;
SELECT 1 AS ignore;'
LANGUAGE sql;
I load it into 8.2.5 - then dump it
Bob Pawley wrote:
I have a table with four columns that will either be null or hold the
value 'true'.
I want to obtain the count of these columns, within a particular row,
that have 'true' as a value (0 to 4).
I have attempted the Select count method but it seems that I need
something more.
Hi
http://forum.myways.su/felt.php?drive=bhankyuytv3630es
brick...@gmail.com
Ben Trewern wrote:
Is there any reason why :
SELECT char_length(to_char(1, '000'));
Gives a result
char_length
-
4
(1 row)
It seems that to_char(1, '000') gives a string " 001" with a space in front.
Is this a bug?
Regards,
Ben
Try formatting the result:
SELECT char_leng
Sean Davis wrote:
On Mar 1, 2005, at 4:23 PM, Michael Romagnoli wrote:
I am new to postgresql, having previously worked with mysql mostly.
What kind of command would I run if I wanted to copy an entire table
(along with renaming it, and, of course, all data from the first table
- some of which is
tony wrote:
Excuse me dear sir. There seems to be about 97% of the world that runs
Windows that does not give you permission to be rude to a tiny minority
who just happen to have written an insanely great database that runs
quite nicely on their "hobby" OSs as well as the crap you call home. If
you
Hi listers!
I'll start with some details:
select version();
PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian
1:3.3.3-5)
Upon compiling a new function that I was working on, I came across an error:
"could not read block 0 of relation 1663/17239/16709: Bad address" whi
Bricklen Anderson wrote:
Hi listers!
I'll start with some details:
select version();
PostgreSQL 8.0.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
(Debian 1:3.3.3-5)
Upon compiling a new function that I was working on, I came across an
error:
"could not read block 0 of rel
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
Once I recompile the function, I no longer get that message. Is there
anything else that I can check or do to make this stop happening? Or is
this a sign of things to come (possible corruption, etc?)
Well, the original error
Tom Lane wrote:
=# select distinct prolang from pg_proc;
prolang
-
12
13
14
17813
63209
63212
63213
63214
(8 rows)
That looks fine ...
=# select * from pg_language ;
Try "select oid,lanname from pg_language".
regards, tom lane
Sorry, I see that I forg
Joe Audette wrote:
Hi,
I have an app that I released with a particular field
as varchar 255.
Can someone give me a script example I can use to make
an upgrade script to change it to text or at least to
larger varchar without losing existing data?
I support 3 different dbs in my app, Postgre is the
Hugo wrote:
hi,
is it possible to schedule the execution of an sql stored procedure in
postgress on linux?
thanks
Hugo
cron job:
eg. Sat 2:30am
30 2 * * Sat psql -d dbname -c "select your_func()"
--
___
This e-mail may be privileged and/or confidential, and the sender
[EMAIL PROTECTED] wrote:
Hi,
I made some tests of plsh with Postgresql 8.0.2 but it seems that it
doesn't work.
What's the easiest way to execute shell commands from a PostgreSQL
function (afraid not possible from pgsql function...).
Regards,
Patrick
Easiest way? No idea.
Another way to do i
Collin Peters wrote:
> The table in question is a simple users table. The details are at the
> bottom of this message. The performance on this table was fine during
> testing with less than 100 users. Then we inserted about 37,000 records
> into the table. Now a 'SELECT * FROM pp_users' takes o
[EMAIL PROTECTED] wrote:
> Hi
>
> I am from a MSSQL background and am trying to understand something about
> statistics in PostgreSQL.
>
> Question 1:
> In MSSQL, if you create an index (and you are using MSSQL's default
> settings) the Server will automatically create appropriate statistics for
wayne schlemitz wrote:
> How do I remove my self from this mail list I have
> tried
> in the past with no luck. Please sent specific
> instructions.
>
> Wayne
>From the web, you could try here:
http://www.postgresql.org/community/lists/subscribe
and click the "unsubscribe" action.
___
germ germ wrote:
> Thank you all for your help. I got it working, once.
>
> Right after I made the change and tested it,
> everything worked perfect. Then I had a freak nose
> bleed- (This has to be my 3rd nose bleed in my life
> ever). I frantically starting closing windows and
> shells. While i
David Fetter wrote:
> On Thu, Jul 28, 2005 at 09:19:49AM -0700, Bryan Field-Elliot wrote:
>
>>We have this simple query:
>>
>>select status, count(*) from customer group by status;
>>
>>There is already a btree index on status, but, the customer table is
>>huge, and this query must be executed ver
Douglas McNaught wrote:
>> Is it possible to write jobs in postgresql & if possible how should I
>>write .please help me.
>
>
> What does "write jobs" mean?
>
I'm assuming this person has an Oracle background, if so, jobs are Oracle's
equivalent to a built-in cron scheduler. This has been disc
I may have missed it in the docs, but were certain timestamp abbreviations
phased out between 8.0.3 and 8.1 beta1?
eg.
(8.0.3)
#SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'PST';
timezone
-
16/02/2001 20:38:40 PST
(8.1beta1)
#SELECT TIMESTAMP '2001-02-16
8.1beta1, linux
If I issue a query from a view with a WHERE condition, w/ EXPLAIN ANALYZE, I am
receiving an error:
dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897;
ERROR: bogus varno: 205
(user_bw is a VIEW)
If I omit the WHERE condition, or the EXPLAIN ANALYZE, or query dir
Tom Lane wrote:
> Bricklen Anderson <[EMAIL PROTECTED]> writes:
>
>>dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897;
>>ERROR: bogus varno: 205
>
>
> Known bug, fixed a week or two back.
>
> regards, tom lane
>
Tom Lane wrote:
> Bricklen Anderson <[EMAIL PROTECTED]> writes:
>
>>8.1beta1, linux
>
>
>>dev#EXPLAIN ANALYZE select sum(bytes) from user_bw where id=33897;
>>ERROR: bogus varno: 205
>
>
> Known bug, fixed a week or two back.
>
>
Emi Lu wrote:
> Greetings,
>
> I am not very familiar with the system views/tables in postgreSQL. I'd
> like to get all table names that have a column let's say named "col1".
>
> For example,
> t1 (... col1 varchar(3) ... )
> t2 (... col1 varchar(3) ... )
> t3 (... ...)
>
>
> After querying the
John D. Burger wrote:
> I can't figure out why the following doesn't work:
>
> select
> (case
> when count1 < 300 then 'Other'
> else country1
> end) as country2,
> sum(count1) as count2
> from (select coalesce(country, 'None') as country1, count(*) as count1
Dan Armbrust wrote:
> Does postgresql have any facility to dump anything more fine grained
> than a database to a text file?
>
> For example, to mention a bad word, MySQL's dump command allows you to
> specify individual tables to dump
> PostgreSQL's pg_dump command seems rather limited in its
Dan Armbrust wrote:
>>
> Now I'm just filling the mailing list with mis-information. It actually
> ignores all but the last -t flag - so this only allows me to specify one
> table at a time, rather than several tables.
>
> I need to write up my use case so the maintainers can see why I want to
>
Noticed this in one of my pg logs last Friday:
LOG: could not fsync segment 0 of relation 1663/16387/22359: Input/output error
ERROR: storage sync failed on magnetic disk: Input/output error
This relation corresponds to a table, which receives thousands of inserts via
COPY every couple of minut
Tino Wildenhain wrote:
> Am Dienstag, den 18.10.2005, 15:31 -0600 schrieb Cristian Prieto:
>
>>Any of you knows is there is any way in pg_dump or anything to dump
>>just the functions from a database?
>
>
> pg_dump -Fc -v -f temp.dump yourdatabase
> pg_restore -l temp.dump | grep FUNCTION >funct
Bob Pawley wrote:
> I am running version 8 on Windows.
>
> Why do I get error messages stating that functions and/or tables do not
> exist when these tables and functions are visible, accessible and very
> much do exist, as called?
>
> Bob Pawley
It would probably help if you supplied some mor
snacktime wrote:
>
> I remember a few months back when someone hit the emergency power switch
> to the whole floor where we host at Internap. Subsequently the backup
> power system had a cascading failure. Livejournal, who also hosts
> there, was up all night and into the next day restoring thei
blackwater dev wrote:
In MySQL, I can use the replace statement which either updates the
data there or inserts it. Is there a comporable syntax to use in
postgreSQL?
I need to do an insert and don't want to have to worry about if the
data is already there or not...so don't want to see if it the
I couldn't find any useful references in the docs or archives for emitting the
line number of a plpgsql function (in a RAISE statement). I'd like to use it for
debugging some complex functions.
Does anyone have any tips on where to look, or an example of this?
Cheers,
Bricklen
--
_
Richard Huxton wrote:
> Bricklen Anderson wrote:
>
>>I couldn't find any useful references in the docs or archives for emitting the
>>line number of a plpgsql function (in a RAISE statement). I'd like to use it
>>for
>>debugging some complex functions.
Jim C. Nasby wrote:
> This seems to be something useful to have... can we get a TODO? Unless
> maybe Bricklen wants to submit a patch... :)
I can barely even spell C ...
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any relat
Jerry Sievers wrote:
> Bricklen Anderson <[EMAIL PROTECTED]> writes:
>
>
>>I couldn't find any useful references in the docs or archives for emitting the
>>line number of a plpgsql function (in a RAISE statement). I'd like to use it
>>for
>>deb
Bob Pawley wrote:
> Hope someone can help me learn.
>
I highly suggest getting an entry level book on SQL and reading that, then going
through the PostgreSQL documentation. This will better equip you to solve these
problems, and no doubt get you much further ahead in a shorter period of time.
--
Script Head wrote:
> In PL/pgSQL the round() function seem to round a number down all the
> time. Is there something like ceil() that would round it up?
>
> ScriptHead
Yup, it's called ceil(). Oh! You just said that ;)
Can also use ceiling()
--
___
This e-mail may
CSN wrote:
I have two tables:
items: id, title, added, ...
news: id, headline, datetime, ...
I'd like to select the latest 25 combined records from both tables. Is there a
way to do this
using just select?
Thanks,
csn
Maybe something like this?
select id,title_headline,dt
from (
select
brian wrote:
Can anybody spot the problem with this function? Or, how i'm calling it?
(it's not clear to me which it is)
CREATE TABLE member (
...
first_name character varying(64),
last_name character varying(64),
organisation character varying(128),
email character varying(128),
...
);
Harpreet Dhaliwal wrote:
Hi,
I have a timestamp field in my table and want to set a default value of
current date/time for it.
What should i enter as its default value? is there any function like
now() in postgres?
Thanks,
~Harpreet
http://www.postgresql.org/docs/8.1/interactive/functions-d
kbajwa wrote:
Hello:
I am going to install Ubuntu OS under their LAMP installation. This LAMP
installation installs mySQL. Is there any way I can install postgreSQL
instead of mySQL? Has anybody created a LAMP to install Ubuntu, Apache,
postgreSQL & PHP?
Kirt
I don't know about a preco
J B wrote:
Guys,
I have a table that has various fields that have whitespace in the
values. I'd like to roll through and strip the left and right whitespace
out of all fields that contain strings.
Is there any easy way to do this?
Thanks!
JB
"trim" will strip the whitespace from both sid
Cabbar Duzayak wrote:
Hi,
We have huge amount of data, and we are planning to use logical
partitioning to divide it over multiple machines instances. We are
planning to use Intel based machines and there is not much updates but
mostly selects. The main table that constitutes this much of data ha
Bob Pawley wrote:
This is the example found in the documentation to dump a database.
"Examples
To dump a database:
$ pg_dump mydb > db.out"
The following - $ pg_dump aurel > aurel.out - gives me
"ERROR: syntax error at or near "$" at character 1"
What am I missing???
Bob
The "$"
snappingturtle wrote:
It appears that in my installation of Postgres that dollaw sign quoting
is disabled. For example, the following command returns an error:
I didn't do anything (that I know of) to disable dollar quoting. Any
advice on how to enable dollar sign quoting?
Are you on pos
Scott Marlowe wrote:
On Thu, 2006-12-07 at 11:59, Tony Caduto wrote:
http://linux.inet.hr/poll_favorite_database.html
So far Firebird is in the lead :-(
Somebody just told their list earlier than anyone told us... or mysql's
list.
http://archives.postgresql.org/pgsql-general/2006-11/msg000
[EMAIL PROTECTED] wrote:
I'm trying to port an MS statement that's a bit involved with
timestamps, and I don't see anything in the docs to lead me forward.
It's basically a select statement, looking for records with a timestamp
within a certain range, where that range is calculated with one of
Marc Evans wrote:
OK, I must be missing something obvious:
c3i=> CREATE OR REPLACE FUNCTION foo_trigger() RETURNS TRIGGER AS $$
c3i$> eval { spi_exec_query('INSERT INTO FOO_BAR VALUES(1)'); } ||
c3i$> spi_exec_query('SELECT 1 as foo');
c3i$> $$ LANGUAGE plperl;
ERROR: creation of Perl funct
Jeanna Geier wrote:
Hello List!
OK, so I'm new to SQL and Postgres and am working on taking over this DB
work, and ran across a command that I'm not sure of and am wondering if you
can help me with... Probably a basic SQL question, but thought more than one
person on here would be able to poin
Emi Lu wrote:
HEllo,
I am looking for a psql method to get the bigger value of two numbers.
For example,
methodName(12.6, 3.8)
select greatest(12.6,3.8);
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropr
Bradley Kieser wrote:
I hope that someone has cracked this one because I have run into a brick
wall the entire week and after 3 all-nighters with bad installations, I
would appreciate hearing from others!
I am looking for a decent OpenSource CRM system that will run with
Postgres. SugarCRM se
Joshua D. Drake wrote:
Martijn van Oosterhout wrote:
No difference whatsoever from PostgreSQL's point of view. Use whichever
distribution is easiest for you to administer. After all, there's no
point installing Postgres on a machine you don't know how to maintain
or tune :)
Actually there is a d
Bruno Almeida do Lago wrote:
OK! I've got to run now, but will search more about it tomorrow.
Could you give me more details / references?
You don't want to do it automatically. You want to do it by hand but it
isn't that hard.
Automatically? How?
About the link between the two databases, where
Hi all,
I recently came across some apparent corruption in one of our databases around
a month ago.
version: postgresql 8 (originally 8r3, now at 8.0.1), debian box
The messages that we were originally getting in our syslog were about invalid
page headers. After
googling around, then dumping the
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
Feb 1 11:17:50 dev94 postgres[4959]: [472-1] 2005-02-01 11:17:50 PST> ERROR:
xlog flush request
972/FC932854 is not satisfied --- flushed only to 73/86D2640
Hmm, have you perhaps played any games with pg_resetxlog in this
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
I would have suggested that maybe this represented on-disk data
corruption, but the appearance of two different but not-too-far-apart
WAL offsets in two different pages suggests that indeed the end of WAL
was up
Bricklen Anderson wrote:
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
I would have suggested that maybe this represented on-disk data
corruption, but the appearance of two different but not-too-far-apart
WAL offsets in two different pages suggests that inde
Alban Hertroys wrote:
Bricklen Anderson wrote:
Any ideas on what I should try next? Considering that this db is not
in production yet, I _do_ have the liberty to rebuild the database if
necessary. Do you have any further recommendations?
I recall reading something in this ML about problems with
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
But anyway, the evidence seems pretty clear that in fact end of WAL is
in the 73 range, and so those page LSNs with 972 and 973 have to be
bogus. I'm back to thinking about dropped bits in RAM or on disk.
Karl O. Pinc wrote:
4. Can I query an object in another database, like in Oracle's dblink?
I'm no expert. I don't believe so. You can query across scheams
in the same database but not across databases. You could do
something (anything!) by writing an external function in C or
whatever, but I c
Peter Futaro wrote:
> Dear PSQL,
>
> I need to make a documentation for my database. The documentation I want
> is almost exactly like the result of "\d" command. I want to make the
> report using a database manager application, and it requires me to make
> my own report by typing the SQL command
Ubence Quevedo wrote:
A friend of mine has created this simple accumulating
loop query for MS SQL 2k5 Express Edition. I am
trying to reproduce the same results with PostgreSQL
8.1, but am not able to find much useful help on how
to properly set up a variable of both int and char.
The PostgreSQ
Pandurangan R S wrote:
To get rid of ^M characters you could use
cat file | tr -d ^M
you need to type ^V before you type ^M in the preceeding command. But
^V will not be displayed on the screen.
Or you can use dos2unix/unix2dos, if installed. I believe they are in
the sysutils package.
---
Jim C. Nasby wrote:
I would highly recommend taking a look at how Oracle is handling
encryption in the database in 10.2 (or whatever they're calling it).
They've done a good job of thinking out how to handle things like
managing the keys.
I know that Oracle magazine did an article on it recently
Kleynhans, Hendrik wrote:
___
“The information contained in this e-mail is confidential and may contain
proprietary information.
It is meant solely for the intended recipient. Access to this e-mail
Bob Pawley wrote:
Hi Folks
I have three triggers and associated functions that fire on one insert
and moves row ID information to five different tables..
I am getting an error message "stack depth limit exceeded".
Is this normal for, what I consider, a small amount of information transfer?
[EMAIL PROTECTED] wrote:
The problem was fixed by initializing the array before giving it a
value. Not surprising Postges isnt as popular as it should be. I was by
luck that I found this out - the manual says nothing about init arrays.
Instead of flippant comments like that, submit docs a patch
Steve Crawford wrote:
How can I dump a function definition with pg_dump?
Background: We often need to create objects that are all relevant to
only a specific project. Sometimes it is a single table. Other times
there are many tables, indexes, views, rules, triggers and functions.
All the obje
Hrishikesh Deshmukh wrote:
Hello All,
Suppose there 3 users red, green, blue. How can the user green know what
tables he has created?!
From psql command line \dt lists every table in the DB!!!
Thanks in advance.
Hrishi
If you mean that the owner of the table(s) is the user "green", then tr
Michael Trausch wrote:
Hey guys,
I'm having a slight problem with this database that I'm trying to setup
on PostgreSQL 8.1.3... What I've got is a stored procedure that refuses
to get itself into the system, and I'm not sure why. It is throwing a
syntax error on DECLARE, but I don't see it. I
Dino Vliet wrote:
Hi guys,
I trying for days to get this simple plpgsql procedure
to run but I keep getting this error:
psql:pgsql_procedure.txt:15: ERROR: syntax error at
or near at character 17
QUERY: copy cancel TO $1 with delimiter as ',' null
as '.'
CONTEXT: SQL statement in PL/PgSQL f
Dino Vliet wrote:
Nope:-(
I added it just now and still the same error message!!
Have you tried it with your "copy" command executed dynamically?
eg. execute 'copy cancel to location ...';
---(end of broadcast)---
TIP 1: if posting/reading throu
Emi Lu wrote:
No. It is not for select.
I have tens of tables with very clean structure. For example, username,
application_code, last_modified_by, etc in specific orders.
Since the business model is changed, I have to add some columns to
serveral tables.
I prefer columns orders followin
sergey kapustin wrote:
Hi all!
can anybody say me what's wrong with this query. I just try to take
unique values from table column and print them in random order
select distinct num from (select 1 as num union select 2 as num union
select 1 as num union select 3) t order by random();
ERROR: for
There was a thread last November entitled "Transitive closure of a
directed graph" on the [HACKERS] list. There may be some information of
use there.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
Anton de Wet wrote:
One problem I see the postresql at the moment (and I'm porbably touching
a can of worms here) is the lack of some sort of certification.
One thing linux (or Red Hat) is doing well is supplying the things that
corporates are looking for. And the first thing they look for w
79 matches
Mail list logo
