logo
IRC Archive / Freenode / #postgresql / 2010 / February / 10 / 1
kemo
hi
bluelaguna
??unix
pg_docbot_adz
For information about 'unix' see:
http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
kaveh
Hi everyone, I am searching (lamely) in google for a while, can anyone tell me when I have a STATEMENT based trigger how can I get the current statement?
Like the NEW parameter in ROW baed triggers
RhodiumToad
no
StuckMojo
hehe, "no, we won't tell you"
i think he means "no, you can't"
copumpkin
if I had a src, dst table representing an arbitrary graph and wanted to fetch all incoming and outgoing edges from a given node in a single query, could a CTE help me there?
where src and dst are just ints representing nodes (and a row is an edge)
RhodiumToad
you don't need a cte for that
copumpkin
well, I wanted to go a level deeper after that
RhodiumToad
UNION is your friend
kaveh
:) Thanks a lot, so it seems it is impossible
RhodiumToad
just one level, or many?
copumpkin
the more general thing I want is to fetch (not recursively): the queried node, all nodes incoming and outgoing from it; all incoming nodes for the nodes discovered from queried nodes outgoings, and vice versa
kind of hard to explain
a bit like http://en.wikipedia.org/wiki/Markov_blanket, except I also want the outgoings of my incomings
Kenaniah
??replication
pg_docbot_adz
For information about 'replication' see:
http://slony.info/ :: http://www.postgres-r.org :: http://www.commandprompt.com/products/mammothreplicator/ :: http://bucardo.org/ :: https://projects.commandprompt.com/public/replicator :: http://wiki.postgresql.org/wiki/Streaming_Replication
copumpkin
is that something I can do with a CTE, or is it something I should still do with some subqueries and some unions?
RhodiumToad
you can do literally anything with a CTE, the only question is whether you're better off with static unions
(CTEs are turing-complete)
copumpkin
I figured I might get more sharing with a CTE
as I need to both retrieve the direct neighbors and use them in a query to find their neighbors
RhodiumToad
copumpkin: that may or may not make up for the overhead
Kenaniah
thoughts on bucardo anyone?
s34n
I'd like to install a pg Windows client
but the downloads seem to be for the whole enchilada
copumpkin
RhodiumToad: hmm
s34n
is there a minimal windows client install somewhere?
just enough to let me connect to a server on another machine?
StuckMojo
pgadmin?
Snow-Man
sadly, I don't think so
yea, you could grab pgadmin, if that's the interface you want
if you want actual psql, you have to pull out the bits you need from the big package that edb provides, iirc
StuckMojo
i imagine the pgadmin3 installer comes with libpq, if not you could grab the pg odbc driver and use any odbc enabled client
s34n
well, not exactly
Snow-Man
yes, pgadmin3 comes with libpq, iirc
s34n
StuckMojo: right idea though
Snow-Man
s34n: what client do you want?
that's probably the first question..
s34n
StuckMojo: I'm trying to connect from AutoCAD :(
StuckMojo
oh, go odbc then
Snow-Man
what does autocad support? odbc?
s34n
which thought it would invent it's own FDO
and the FDO provider for postgis doesn't seem to actually include the dlls for connecting
kemo
have to load ~100 GB into 5 different dbms an record which performs better in terms of speed (sec). is ther some kind of "best practise" or a super special universally usable bulk loading tool?
Snow-Man
s34n: that's kind of curious. My guess is that it needs libpq...
StuckMojo
IME almost anything windows can talk odbc
Snow-Man
(Action) doesn't know much about FDO
kemo: how about "pick a database based on your actual requirements rather than some arbitrary metric"
breinbaas
kemo: most dbms have their own superspecialised bulkloader
s34n
Snow-Man: yes. it does
breinbaas
the comparison can be interesting in itself, if you can put enough time into tuning each system
s34n
Snow-Man: more curiouser, it also needs MSVCR90.dll
breinbaas
kemo: which systems?
kemo
win server 2003
Snow-Man
indeed.. PG has COPY and pg_restore, Oracle has imp, Mysql has 'load data'...
breinbaas
sybase, bcp
StuckMojo
s34n: that would mean it expects that your libpq was built with VC, which is not surprising
Snow-Man
StuckMojo: odd that they don't distribute it
StuckMojo
s34n: the 'R' is redistributable, so you can get that dll easily
Snow-Man
I would guess it comes w/ pgadmin3 tho..
s34n
I should be able to grab MSVCR90.dll and IESHIMS.dll
I just scratch my head that they aren't included in their silly provider to begin with
Snow-Man
they're probably expecting that you've installed the whole PG system
and don't want to have duplicate/possibly different versions, running around
StuckMojo
that or they didn't realize they were linked to it because it was down the chain or something
it's easy not to realize
Snow-Man
sadly, windows sucks, and doesn't have the kind of nice packaging system that real systems, like Debian, do
StuckMojo
most machines you test on have VC installed, and thus already have it
so you don't realize you need it until you're at a client site doing an install ;)
PITA
s34n
If only I could replace AutoCAD with some nice Unix-based solution...
StuckMojo
blender?
heh
Snow-Man
blender is pretty cool
s34n
(Action) chuckles
blender gis would be interesting
Snow-Man
autocad does gis?
been a long time, but I don't remember lat/longs in autocad. :)
breinbaas
can someone clean this up? http://pgsql.privatepaste.com/7848a3cc44 (xlog monitoring SQL)
there is just no way to get info from the master db, is there?
Snow-Man
huh, autocad map 3d does look kinda neat
perry81
yes
Snow-Man
breinbaas: might be cleaner as a pl/pgsql function..
breinbaas
yes, that's probably better
quentusrex_
I'm trying to drop a database, but there are still connections
how do I disconnect all sessions and drop the database?
I'm trying to restore from a backup...
ads
quentusrex_: stop all applications
quentusrex_: including your own connection.
quentusrex_: you can connect to another database (like "postgres" or "template1")
sfuentes
anyone how i can get the status result of an EXECUTE statement in a stored procedure?
quentusrex_
ads, how do I force it off?
I can't find the one remote user who has his pgadmin3 still connected.
ads
select * from pg_stat_activity;
quentusrex_
and how do I force off the connections?
ads
shutdown your db, modify pg_hba.conf and reject all new connections for this database.
Kenaniah
quentusrex: if your server consists of only one database, you can "pg_ctl stop -m immediate", create your recovery file, and start
codeaholic
quabbin_: you can always kill their postgres process
look for the pid from pg_stat_activity and kill -9 it
ads
codeaholic: NO
Never ever kill -9 a postmaster
-1 is enough
codeaholic
okay HUP it
Kenaniah
doesn't hup just tell it to re-ready config?
*re-read the config?
ads
You risk a corrupt shared memory
codeaholic
ah
ads
Kenaniah: not for the single postmaster prozess
codeaholic
Kenaniah: that's just by convention. a process can do anything it wants in the HUP signal handler
ads
codeaholic: please don't give such advises if you don't know what your advise is doing!
codeaholic
ads: we all make mistakes. even you
Kenaniah
codeaholic: that was a pretty catastrophic mistake =P
codeaholic
chill out. you were here to save the data. contrats captain awesome
ads
codeaholic: yeah, even me.
codeaholic
My _POINT_ was that you can kill the process associated with his connection and disconnect him
ads
sure - if you kill -9 it, you can even end up with having the database closing all connections and doing a recovery.
copumpkin
this looks pretty hardcore: http://pastie.org/817418
anyone have any suggestions?
endpoint_david
copumpkin: describe what your query is attempting to accomplish
copumpkin
endpoint_david: for a given node, get all edges involving: its successors, its predecessors, its successors' predecessors, and its predecessors' successors
BlueAidan_work
??tweakers
pg_docbot_adz
For information about 'tweakers' see:
http://tweakers.net/reviews/649/7
http://tweakers.net/reviews/661/7
copumpkin
there are no seq scans, which makes me happy
but I'm not sure if it's as efficient as it can be
(it's going to be running on a huge table)
oranges
ok raid 10 for db
now how bad will it be to set that up under linux
like software raid
copumpkin
lines 36,37 and 41,42 seem identical... is that shared?
oranges
I haven't done 10
copumpkin
how bad would raid 6 be for a database?
oranges
I was told raid 10 is bestest
raid 5 bad write speed
copumpkin
yeah
makes sense
oranges
I love big women.
copumpkin
good to know.
oranges
postgresql seems to be able to handle some serious heavy lifting
I am guna install it tomorrow
jay321
folks is it possible to have a private/local and public schema, where one table exists in both spaces, as a local version, then public version? in oracle, you can have a user$foo table, then a public$foo table as well. all sql acts on the user$foo by default.
endpoint_david
jay321: yes
the resolution is determined by the search_path, which is usually $user,public
jay321
endpoint_david, ok thank you for your quick response, what is this set up referred to in the docs?
endpoint_david
??schema
pg_docbot_adz
For information about 'schema' see:
endpoint_david
??search_path
pg_docbot_adz
For information about 'search_path' see:
jay321
pretty cool, thank you again :D
endpoint_david
probably that last one will be most useful
neruda_
when i run 'postgres -D $somevar >logfile 2>&1 &' I get an error in logfile that says: FATAL: unrecognized configuration parameter "max_fsm_pages"
any thoughts?
anyone awake in here
Snow-Man
perhaps
I don't typically run postgres that way, heh.
path
#define awake.h
Snow-Man
looks like you're using a version which doesn't have max_fsm_pages as a config option in any case though
I hope you're not trying to do an in-place upgrade between major versions, that's not typically supported.. You can do it with an add-on utility between 8.3 and 8.4, but that's about it...
jmoiron
hi all; what determines the unix socket that postgresql runs on? something seemingly switched it up on me (maybe an upgrade) and i ran into trouble for a bit
solved (for now?) but would like to know for the future; it appears mine is named .s.PGSQL.5433 now, but my app was expecting 5432
Snow-Man
are you running Debian?
Try checking pg_lsclusters ?
jmoiron
it says 5432 is down..
Snow-Man
basically, the # in the unix socket name is derived from the port number
jmoiron
not quite; yeah, i figured that, since 5432 is the default port
Snow-Man
perhaps you did a major-version upgrade and the old cluster is still configured on the default port..?
jmoiron
perhaps; i thought i had done so a while ago.. it says the main cluster is down
but i have one running that pg_lsclusters doesn't list
or.. i have something running, on that alternate port, that appears to be functional; and other data directories
Snow-Man
sounds kinda messy
did you install from source or something?
jmoiron
it's not anything close to mission critical just wondering how things went pear.. no, I just don't recall an upgrade recently other than one from 8.3 -> 8.4
which are completely separated on debian
Snow-Man
erm, they'd both show up in pg_lsclusters ...
if you used the Debian packages and whatnot, which I would recommend
beren:/home/sfrost> pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
jmoiron
i did
Snow-Man
8.3 main 5432 online postgres /var/lib/postgresql/8.3/main /var/log/postgresql/postgresql-8.3-main.log
8.4 main 5433 online postgres /var/lib/postgresql/8.4/main /var/log/postgresql/postgresql-8.4-main.log
heh.
we're still working on our migration from 8.3 to 8,4. :)
jmoiron
i don't see that; i see this
well
the same as your second line
except the status is 'down'
Snow-Man
hrmpf.
commx
i'm a bit confused about how pgsql deals with integers. do I have to use int8 to store unsigned integers > 2 billion?