Following README leads to "column 'payload' doesn't exist" about akka.persistence.postgresql

[Aaronontheweb]

Hi @SeanKilleen,

could you post your exact HOCON configuration - without the connection strings, if they're sensitive?

[SeanKilleen]

@Aaronontheweb sure thing, it's below -- sorry, should have done that up front. The conn strings aren't sensitive at all, so no worries:

  <akka>
    <hocon>
      <![CDATA[
          akka { 
            loglevel=INFO,  
            loggers=["Akka.Logger.Serilog.SerilogLogger, Akka.Logger.Serilog"]
            }
            akka.persistence{
	              journal {
		              plugin = "akka.persistence.journal.postgresql"
		              postgresql {
			              # qualified type name of the PostgreSql persistence journal actor
			              class = "Akka.Persistence.PostgreSql.Journal.PostgreSqlJournal, Akka.Persistence.PostgreSql"

			              # dispatcher used to drive journal actor
			              plugin-dispatcher = "akka.actor.default-dispatcher"

			              # connection string used for database access
			              connection-string = "Server=localhost;Port=5432;User Id=admin;Password=example;Database=portfolioss;"

			              # default SQL commands timeout
			              connection-timeout = 30s

			              # PostgreSql schema name to table corresponding with persistent journal
			              schema-name = public

			              # PostgreSql table corresponding with persistent journal
			              table-name = event_journal

			              # should corresponding journal table be initialized automatically
			              auto-initialize = off
			
			              # timestamp provider used for generation of journal entries timestamps
			              timestamp-provider = "Akka.Persistence.Sql.Common.Journal.DefaultTimestampProvider, Akka.Persistence.Sql.Common"
		
			              # metadata table
			              metadata-table-name = metadata

			              # defines column db type used to store payload. Available option: BYTEA (default), JSON, JSONB
			              stored-as = BYTEA
		              }
	              }

	              snapshot-store {
		              plugin = "akka.persistence.snapshot-store.postgresql"
		              postgresql {
			              # qualified type name of the PostgreSql persistence journal actor
			              class = "Akka.Persistence.PostgreSql.Snapshot.PostgreSqlSnapshotStore, Akka.Persistence.PostgreSql"

			              # dispatcher used to drive journal actor
			              plugin-dispatcher = ""akka.actor.default-dispatcher""

			              # connection string used for database access
			              connection-string = "Server=localhost;Port=5432;User Id=admin;Password=example;Database=portfolioss;"

			              # default SQL commands timeout
			              connection-timeout = 30s

			              # PostgreSql schema name to table corresponding with persistent journal
			              schema-name = public

			              # PostgreSql table corresponding with persistent journal
			              table-name = snapshot_store

			              # should corresponding journal table be initialized automatically
			              auto-initialize = on
			
			              # defines column db type used to store payload. Available option: BYTEA (default), JSON, JSONB
			              stored-as = BYTEA
		              }
	              }
              }
      ]]>
    </hocon>
  </akka>

[SeanKilleen]

More code for reference --

The code to init the actor, running inside of a quick console app:

var rando = actorSystem.ActorOf(Props.Create<MyActor>(), "rando-actor");
rando.Tell("Message1");
rando.Tell("message2");

And the actor code, which I think is straight out of the example:

    public class MyActor : ReceivePersistentActor
    {
        public class GetMessages { }

        private List<string> _msgs = new List<string>(); //INTERNAL STATE

        public override string PersistenceId
        {
            get
            {
                return "HardCoded";
            }
        }

        public MyActor()
        {
            // recover
            Recover<string>(str => _msgs.Add(str));

            // commands
            Command<string>(str => Persist(str, s =>
            {
                _msgs.Add(str); //add msg to in-memory event store after persisting
            }));

            Command<GetMessages>(get => Sender.Tell(new List<string>(_msgs)));
        }
    }
}

[SeanKilleen]

Didn't realize postrges was spitting out helpful errors in the console.

It looks like there's a query attempting to hit a payload column in the snapshot table.

portfolioss-db | 2018-02-23 14:39:50.902 UTC [115] ERROR:  column "payload" does not exist at character 159
portfolioss-db | 2018-02-23 14:39:50.902 UTC [115] STATEMENT:
portfolioss-db |                        SELECT persistence_id,
portfolioss-db |                            sequence_nr,
portfolioss-db |                            created_at,
portfolioss-db |                            manifest,
portfolioss-db |                            payload,
portfolioss-db |                            serializer_id
portfolioss-db |                        FROM public.snapshot_store
portfolioss-db |                        WHERE persistence_id = $1
portfolioss-db |                            AND sequence_nr <= $2
portfolioss-db |                            AND created_at <= $3
portfolioss-db |                        ORDER BY sequence_nr DESC

This column is not included in the SQL setup in the docs.

So it seems either:

• There's a bug in the code looking for that column in that table
• There's a missing line in the README creation docs
• There's a missing step in the README migration docs.

Running an ALTER and adding the column appears to get it to work correctly..

@Aaronontheweb when you determine which of the above is the issue, let me know and I'll try to do a quick PR to address.