AlliedModders - PostgreSQL Database Interface

AlliedModders (https://forums.alliedmods.net/index.php)

- Extensions (https://forums.alliedmods.net/forumdisplay.php?f=134)

- - PostgreSQL Database Interface (https://forums.alliedmods.net/showthread.php?t=236415)

PostgreSQL Database Interface

This driver is now included in SourceMod starting with version 1.11.0.6666.

This extension provides PostgreSQL database support to SourceMod. There is a bug report open for this, but i don't use pgsql so i can't test it in a production environment. Putting it up here to see if someone got a use for it and confirms it does what you expect.

The extension is currently compiled against postgresql 9.3.2.
It registers a "pgsql" database driver. To use this, just set the "driver" key in the databases.cfg section to "pgsql" like this:

Code:

    "pgtest"

    {

        "driver" "pgsql"

        "host" "localhost"

        "database" "my_database"

        "user" "user"

        "pass" "password"

    }

Installation
1. Drag & Drop

Due to syntax differences in the SQL flavours between MySQL, sqlite and PostgreSQL you can't be sure that all plugins will run out of the box with pgsql. You might need to slightly alter your queries depending on the used database driver. Just like you already have to do with different sql syntax between mysql and sqlite!

Latest builds against SM 1.10 and PostgreSQL 9.6.9 HERE.

This extension is compiled against SourceMod 1.6 and won't run on SM 1.5. This is due to some refactoring of the DBI system in 1.6 and me being too lazy to backport the extension to 1.5!

Re: PostgreSQL Database Interface

Sorry if this is a newb question but what is the big difference between MySql and PostgreSQL
Also do they use the same sql queries, format etc?

Re: PostgreSQL Database Interface

Quote:

Originally Posted by hamza47sohail (Post 2109286)

Sorry if this is a newb question but what is the big difference between MySql and PostgreSQL
Also do they use the same sql queries, format etc?

Using google is not hard :grrr:
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
https://www.scriptrock.com/articles/postgres-vs-mysql/
https://www.udemy.com/blog/mysql-vs-postgresql/

Re: PostgreSQL Database Interface

Quote:

Originally Posted by hamza47sohail (Post 2109286)

Sorry if this is a newb question but what is the big difference between MySql and PostgreSQL
Also do they use the same sql queries, format etc?

Aside from the links posted by Oshizu, just stick with MySQL if you don't already know what PostgreSQL is or how it works.

Re: PostgreSQL Database Interface

Can i recompile with 9.4 driver easily?

Re: PostgreSQL Database Interface

(This is a repost from a PM I sent, in case anyone else has this issue)

Hello Peace-Maker,

I was wondering about the current state of the PostgreSQL driver for sourcemod. When I do threaded queries against it, the server crashes and gives me this error.

Quote:

Assertion Owner() != GetCurrentThreadId() failed.

The driver appears similar in structure to the MySQL driver, so I assumed that threading was implemented.

Thank you for your time,
printf

P.S. I forgot to mention that I also rebased it on 1.7-dev. The patches applied okay, and I didn't think there would be problems. (Since there's not much recent commit history to the MySQL driver)

EDIT2: It also happens when I rebase to 1.6-dev.

Re: PostgreSQL Database Interface

This is compiled against PostgreSQL 9.6.0. Give it a try and see if it still crashes! It's compiled against SM master so requires SM 1.8+.
https://github.com/alliedmodders/sourcemod/pull/32

Re: PostgreSQL Database Interface

Quote:

Originally Posted by Peace-Maker (Post 2464496)

This is compiled against PostgreSQL 9.6.0. Give it a try and see if it still crashes! It's compiled against SM master so requires SM 1.8+.
https://github.com/alliedmodders/sourcemod/pull/32

It still crashes for some reason, I have the backtrace if you need it:

Code:

(gdb) bt

#0  0xf774aae9 in __kernel_vsyscall ()

#1  0xf7470687 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56

#2  0xf7473ab3 in __GI_abort () at abort.c:89

#3  0xf74697c7 in __assert_fail_base (fmt=0xf75a6df4 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", 

   assertion=assertion@entry=0xea3dc23c "Owner() != GetCurrentThreadId()", 

   file=file@entry=0xea3dc1f8 "/home/jannik/dev/sourcemod-peace/public/amtl/amtl/am-thread-utils.h", 

   line=line@entry=140, function=function@entry=0xea3dc2f7 <ke::Lockable::Lock()::__PRETTY_FUNCTION__> "void ke::Lockable::Lock()") at assert.c:92

#4  0xf7469877 in __GI___assert_fail (assertion=0xea3dc23c "Owner() != GetCurrentThreadId()", 

   file=0xea3dc1f8 "/home/jannik/dev/sourcemod-peace/public/amtl/amtl/am-thread-utils.h", 

   line=140, function=0xea3dc2f7 <ke::Lockable::Lock()::__PRETTY_FUNCTION__> "void ke::Lockable::Lock()") at assert.c:101

#5  0xea3c2d07 in ke::Lockable::Lock (this=0xea634390) at /home/jannik/dev/sourcemod-peace/public/amtl/amtl/am-thread-utils.h:140

#6  0xea3c2c02 in PgDatabase::LockForFullAtomicOperation (this=0x9459050) at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgDatabase.cpp:323

#7  0xea3c27e8 in PgDatabase::SetLastIDAndRows (this=0x9459050, insertID=0, affectedRows=0) at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgDatabase.cpp:168

#8  0xea3c22c4 in PgQuery::PgQuery (this=0xea633708, db=0x9459050, res=0xea633668) at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgBasicResults.cpp:305

#9  0xea3c29a9 in PgDatabase::DoQuery (this=0x9459050, query=0x97a7918 "SELECT SteamID, ccname, ccmsgcol, ccuse FROM players WHERE ccuse != 0") at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgDatabase.cpp:252

#10 0xedb5e0e2 in TQueryOp::RunThreadPart() () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#11 0xedb84520 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#12 0xedb84578 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#13 0xedb844e4 in g_DbgReporter () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#14 0xedb84578 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#15 0xedb844e4 in g_DbgReporter () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#16 0xedb84520 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so

#17 0x097a77f0 in ?? ()

Re: PostgreSQL Database Interface

I can't frequently fail to upload files to the forum. Try this one.

Re: PostgreSQL Database Interface

Quote:

Originally Posted by Peace-Maker (Post 2466772)

I can't frequently fail to upload files to the forum. Try this one.

Thank you, that appears to have fixed the issues with threaded queries.

I've converted all of my queries to work in PostgreSQL, but I still get odd things like this in parsing:

Code:

row number 135 is out of range 0..134

...

row number 4 is out of range 0..3

...

row number 4292 is out of range 0..4291

Is there another bug, or is this because MySQL indexes starting from 1 and PostgreSQL indexes starting from 0?