ADUG Melbourne March 2024 Meeting

Meetings
1

Hi Everyone,

Sorry this is a bit late.
The Melbourne ADUG March meeting is Tomorrow Night, Monday 18th March.

I will be having a brief look at Postgresql from a (very) beginners view.
And the somewhat interesting issue I initially ran into with a very basic query.

I thought we could also have a discussion on memory handling in Delphi which seems to have been of interest in the forum recently.
We all have variations around how we do this.

For me, strings that just work, and I use them, and don’t think about them is just so nice…

There is other (often 3rd party) utility functionality, that works similarly well.

We will start with a brief “Public Soap Box” where attendees can ask questions, share discoveries or talk about code.

When: 6:00pm for a 6:15pm start
Where: At the Melbourne Men’s Shed, and on Zoom.

Zoom link will be up here shortly before the meeting starts.

1 Like
2

Topic: ADUG Melbourne Meeting 2024
Time: Mar 18, 2024 06:00 PM Canberra, Melbourne, Sydney

Join Zoom Meeting

Meeting ID: 874 4566 6437
Passcode: 093425

One tap mobile
+61861193900,87445666437#,*093425# Australia
+61871501149,87445666437#,*093425# Australia

Dial by your location
• +61 8 6119 3900 Australia
• +61 8 7150 1149 Australia
• +61 2 8015 6011 Australia
• +61 3 7018 2005 Australia
• +61 7 3185 3730 Australia
• +1 360 209 5623 US
• +1 386 347 5053 US
• +1 507 473 4847 US
• +1 564 217 2000 US
• +1 646 558 8656 US (New York)
• +1 646 931 3860 US
• +1 669 444 9171 US
• +1 669 900 9128 US (San Jose)
• +1 689 278 1000 US
• +1 719 359 4580 US
• +1 253 205 0468 US
• +1 253 215 8782 US (Tacoma)
• +1 301 715 8592 US (Washington DC)
• +1 305 224 1968 US
• +1 309 205 3325 US
• +1 312 626 6799 US (Chicago)
• +1 346 248 7799 US (Houston)

Meeting ID: 874 4566 6437
Passcode: 093425

Find your local number: Zoom International Dial-in Numbers - Zoom

3

@Graeme Would you mind repeating the function signature you mentioned in the meeting ?

(I know it had the msg and the result, but I didn’t want to quote it incorrectly.)

4

Hi Paul

Are you interested in just the signature or would you like and example of how it is used?

The sound was not too good at my end last night (even when I called in) so I am not exactly sure what you are interested in.

Regards
Graeme

1 Like
5

Yes sure, definitely the full context if you’re ok with it.

I think the VCL uses similar sorts of calls.
I think we can compare and contrast in the light of the memory / program safety discussion going on.

Actually having written that … I could just not be lazy and go find an example in the Delphi libraries.

6

Hi Paul

I can supply an example of what I do but I didn’t know the discussion was about memory/program safety.
I was under the impression that it was about different coding methods and I am not sure how my comments would affect safety considerations.

I was going to supply and example of updating a record on a table, starting with the client code and then the server code.

Not sure if this is what you require.

Regards
Graeme

1 Like
7

Notes from Postgresql demo/chat at last nights ADUG Melbourne meeting.

Had a bit of a demo/talk about Postgresql and then a bit on current hot
topic of memory (safety)

Postgresql seems to be the premiere open source DBMS.

It has many datatypes that can be used, as well as options to compose
additional types.

Its Gui Interface, pgadmin 4 is very full featured.
It is moderately fat.

One downside compared with Firebird is no embedded mode.

I Originally started looking at it, because it comes with encryption as
standard, and also it has been on my list of databases to look at for a
long time.

Firedac comes with drivers to drive Postgresql.

Postgresql has odbc compatibility.

There are a number of commercial providers of database drivers for
delhpi for Postgresql.

https://www.postgresql.org/download/products/2-drivers-and-interfaces/

Zeoslib also has support for Postgresql.

Version 15 of Postgresql (The one I installed) has changed handling of
numbers followed by a non numeric value. Previous versions it assumed it
was a word following the number, now it doesn’t like them, and needs the
space.

Firedac on the versions of Delphi I tested (10.1, 11.2) has a bug/issue where
it generates code that ends in something like 1234566ORDER BY OID:

'SELECT OID, ENUMLABEL ’ +
'FROM PG_CATALOG.PG_ENUM ’ +
'WHERE ENUMTYPID = ’ + sId +
‘ORDER BY OID’); ← Needs a space in front of ORDER

It (presumably) used to work with older versions of Postgresql, but now
invokes an exception.

Copying and altering the offending source file, compiling it to a dcu
and including this in the build got it to work ok.

This was found by inspecting a trace from Wireshark.
Which was in itself quite interesting.
(Conveniently encryption not currently/default enabled)

Input query was a simple ‘select * from table;’
That query was there in the trace, but there is SO MUCH stuff that
Firedac wraps around it.

Also while testing, I found Firedac didn’t handle fields of type TEXT
well. I rarely use text/blob fields, and much less likely arrays of
them. I was able to use cast to work around the issue at least in the
short term.

Also on Delphi 10.1 it could not handle a field of type TSVECTOR though
this field type appeared to be recognized in Delphi 11.2.
PostgreSQL.pdf (37.1 KB)

(Memory section to follow)

3 Likes
8

ForADUG.txt.pas (6.6 KB)
Hi Paul

Attached is an incomplete example of my code to add an attachment to a history record. An attachment consists of a description, datetime and a list of files (really the contents of files).

The logic uses my routines to convert TDatasets to and from JSON strings.

Hope this helps.

Please let me know if you need further clarification.

Regards
Graeme

1 Like
9

Thanks so much, @Graeme.

I wasn’t tuned out … I spent some time yesterday searching all the RTL and Spring4D directories with a regex grep to pull out lines with multiple out parameters.

(And incidentally, saw a lot of partial comments in the code about unrelated stuff that looks interesting too)

I had gone through and thrown away the non-function lines, but haven’t had a chance to go back and pull up the individual sections of code to get an impression of how it gets handled in the Delphi codebase.

If I can say anything coherent about it, I was going to post that into the Memory Safety thread.

I’m very interested to look at your usage as well, but I didn’t want to give an impression of (negative) criticism.

10

We switched from MSSQL to Postgres about 10 years ago and its been one of the best decisions we’ve made.

Some points for newcomers:

Postgres, for some reason, comes fairly untuned out of the box. Spend time googling how to optimise it before you do any performance tests.

Avoid Firedac and go straight to PgDac from Devart. Devart has been a partner of ours for almost 20 years and they’ve helped us deliver world class software to some of Australia’s biggest companies. Their focus on performance, keeping up-to-date with the latest releases and having awesome support make them the best choice for database connectivity.

Try not to just use Postgres in an SQL agnostic way if you’re building something big. Postgres has amazing features that are outside the standard SQL syntax that can deliver some very special results. I recommend you start with a Postgres book to get started as they tend to take you through a large range of topics in a logical order compared with coming to Postgres with your previous database learnings and use google to ‘solve problems’. This will help you design for the capabilities of Postgres rather than using your prior experience that may be limited by your previous database systems feature limitations.

If you need to send JSON to a browser, get Postgres to generate the JSON to make your output so much faster and avoid the Delphi overhead.

Postgres works best on Linux. Go with a very modern kernel like Centos9 and don’t be scared to use the newest version (Postgres 16) as each release of late has had some really good new features yet is rock solid. CSV import, which was always fast as, is up to 300% faster in Postgres 16.

For me, having an embedded database engine has very limited use-cases. Postgres can be installed silently just like Firebird if you are using the ‘server’ mode of Firebird which I find much safer and then is invisible essentially, yet with the power of an enterprise grade database.

3 Likes
11

Sorry I missed the meeting (had something else on).

I’ll chime here with another :+1: for Postgresql - we’ve been using it for 10+ years now and it’s been very reliable. We bundle a copy (currently v16.2) with Continua CI and we have had zero issues with postgres that were not caused by either our code or the customer’s environment (out of disk space etc).

Postgresql works well on windows - it might look a bit strange with the multiple processes - but as @hsvandrew pointed out it does take some tuning - it does use less memory than sql server (one of the things that is easily tuned). I’m also using it on linux for my package manager project - performance is fantastic.

As for the lack of embedded mode, never found that to be a problem - you can lock the server down so that it can only be access on the same host (we do this).

Sadly we are still supporting sql server, so haven’t been able to take advantage of some of the newer features.

I can’t comment on delphi clients - haven’t used it with delphi (only used with c#/npgsql).

pgAdmin is pretty good for a free tool - just a bit slow (it’s an electron app I think). If you are after something a bit better dbForge for Postgresql is not too expensive. I’m using the free version but thinking about getting the paid version.

1 Like